import type pg from 'pg';
import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, readJsonBody, requiredString, stringParam } from '../../core/request.js';
import { query, transaction } from '../../core/db.js';
import { autoGrantBadges, type AutoBadgeGrant } from '../profile/badges.js';
import { createUserNotification } from '../notifications/service.js';
import {
  requireTenantAdmin,
  requireTenantPermission,
  type TenantAdminAuth,
} from './auth.js';

type JsonBody = Record<string, unknown>;

const REPORT_STATUSES = ['pending', 'accepted', 'rejected', 'resolved', 'closed'];
const REPORT_PRIORITIES = ['low', 'normal', 'high', 'urgent'];
const FEEDBACK_REPORT_RANGES: Record<string, number> = {
  '7d': 7,
  '30d': 30,
  '90d': 90,
};
const REPORT_STATUS_LABELS: Record<string, string> = {
  pending: '待处理',
  accepted: '已受理',
  rejected: '未采纳',
  resolved: '已解决',
  closed: '已关闭',
};

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 intValue(value: unknown, fallback: number) {
  const numberValue = Number(value ?? fallback);
  return Number.isFinite(numberValue) ? Math.trunc(numberValue) : fallback;
}

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

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

function boundedIntParam(ctx: RequestContext, name: string, fallback: number, min: number, max: number) {
  const parsed = Number(ctx.url.searchParams.get(name) || fallback);
  if (!Number.isFinite(parsed)) return fallback;
  return Math.max(min, Math.min(max, Math.trunc(parsed)));
}

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 addDaysKey(dateKey: string, days: number) {
  const date = new Date(`${dateKey}T00:00:00+08:00`);
  date.setUTCDate(date.getUTCDate() + days);
  return shanghaiDateKey(date);
}

function shanghaiDayStartIso(dateKey: string) {
  return new Date(`${dateKey}T00:00:00+08:00`).toISOString();
}

function feedbackReportRange(value: string) {
  const timeRange = value || '30d';
  const days = FEEDBACK_REPORT_RANGES[timeRange];
  if (!days) throw new HttpError(400, 'Unsupported feedback report range', 'INVALID_FEEDBACK_REPORT_RANGE');
  const endDate = shanghaiDateKey();
  const startDate = addDaysKey(endDate, -(days - 1));
  return {
    timeRange,
    days,
    startDate,
    endDate,
    startAt: shanghaiDayStartIso(startDate),
    endAt: shanghaiDayStartIso(addDaysKey(endDate, 1)),
  };
}

function ratio(numerator: number, denominator: number) {
  if (!denominator) return 0;
  return Number((numerator / denominator).toFixed(4));
}

function feedbackNotificationSeverity(status: string) {
  if (status === 'resolved') return 'success';
  if (status === 'rejected') return 'warning';
  return 'info';
}

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;
}

async function recordAudit(
  client: pg.PoolClient,
  auth: TenantAdminAuth,
  action: string,
  targetType: string,
  targetId: string | null,
  details: Record<string, unknown> = {},
) {
  await client.query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2, $3, $4, $5, $6::jsonb)
    `,
    [auth.tenantId, auth.userId, action, targetType, targetId, JSON.stringify(details)],
  );
}

async function ensureTenantReference(
  client: pg.PoolClient,
  tableName: 'regions' | 'schools',
  tenantId: string,
  id: string | null,
  errorCode: string,
) {
  if (!id) return;
  const result = await client.query<{ id: string }>(
    `select id from public.${tableName} where tenant_id = $1 and id = $2 limit 1`,
    [tenantId, id],
  );
  if (!result.rows[0]) throw new HttpError(400, `${tableName} id is not in this tenant`, errorCode);
}

export async function tenantExamDatesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const regionId = stringParam(ctx, 'regionId');
  const schoolId = stringParam(ctx, 'schoolId');
  const isActive = ctx.url.searchParams.get('isActive');
  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}::uuid`);
  }
  if (schoolId) {
    params.push(schoolId);
    filters.push(`school_id = $${params.length}::uuid`);
  }
  if (isActive === 'true' || isActive === 'false') {
    params.push(isActive === 'true');
    filters.push(`is_active = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select id, legacy_id as "legacyId", region_id as "regionId", school_id as "schoolId",
           exam_name as "examName", exam_date as "examDate", exam_type as "examType",
           description, metadata, sort_order as "sortOrder", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.exam_dates
    where ${filters.join(' and ')}
    order by exam_date asc nulls last, sort_order asc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function upsertTenantExamDateRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'marketing:write');
  const body = await readJsonBody(ctx);

  const item = await transaction(async client => {
    const regionId = nullableString(body.regionId);
    const schoolId = nullableString(body.schoolId);
    await ensureTenantReference(client, 'regions', auth.tenantId, regionId, 'REGION_NOT_FOUND');
    await ensureTenantReference(client, 'schools', auth.tenantId, schoolId, 'SCHOOL_NOT_FOUND');

    const result = await client.query(
      `
      insert into public.exam_dates (
        id, tenant_id, region_id, school_id, legacy_id, exam_name, exam_date,
        exam_type, description, metadata, sort_order, is_active
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3::uuid, $4::uuid, $5, $6, $7::date,
        $8, $9, $10::jsonb, $11, $12
      )
      on conflict (id)
      do update set region_id = excluded.region_id,
                    school_id = excluded.school_id,
                    legacy_id = coalesce(excluded.legacy_id, public.exam_dates.legacy_id),
                    exam_name = excluded.exam_name,
                    exam_date = excluded.exam_date,
                    exam_type = excluded.exam_type,
                    description = excluded.description,
                    metadata = excluded.metadata,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    updated_at = now()
      where public.exam_dates.tenant_id = excluded.tenant_id
      returning id, legacy_id as "legacyId", region_id as "regionId", school_id as "schoolId",
                exam_name as "examName", exam_date as "examDate", exam_type as "examType",
                description, metadata, sort_order as "sortOrder", is_active as "isActive",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        regionId,
        schoolId,
        nullableString(body.legacyId),
        requiredString(body, 'examName'),
        nullableString(body.examDate),
        nullableString(body.examType),
        nullableString(body.description),
        JSON.stringify(objectValue(body.metadata)),
        intValue(body.sortOrder, 0),
        boolValue(body.isActive, true),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Exam date not found for this tenant', 'EXAM_DATE_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.exam_date.upserted', 'exam_dates', result.rows[0].id, {
      examName: result.rows[0].examName,
      examDate: result.rows[0].examDate,
    });
    return result.rows[0];
  });

  return { item };
}

export async function tenantFeedbacksRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'feedback:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const type = stringParam(ctx, 'type');
  const questionId = stringParam(ctx, 'questionId');
  const userId = stringParam(ctx, 'userId');
  const params: unknown[] = [auth.tenantId];
  const filters = ['r.tenant_id = $1'];
  if (status) {
    if (!REPORT_STATUSES.includes(status)) throw new HttpError(400, `Invalid report status: ${status}`, 'INVALID_REPORT_STATUS');
    params.push(status);
    filters.push(`r.status = $${params.length}`);
  }
  if (type) {
    params.push(type);
    filters.push(`r.type = $${params.length}`);
  }
  if (questionId) {
    params.push(questionId);
    filters.push(`r.question_id = $${params.length}::uuid`);
  }
  if (userId) {
    params.push(userId);
    filters.push(`r.user_id = $${params.length}::uuid`);
  }
  params.push(limit);

  const items = await query(
    `
    select r.id, r.question_id as "questionId", q.type as "questionType",
           r.user_id as "userId", u.name as "userName", u.phone as "userPhone",
           r.type, r.category, r.title, r.description, r.status, r.priority,
           r.contact, r.attachments, r.metadata, r.resolution,
           r.handled_by as "handledBy", handler.name as "handledByName",
           r.handled_at as "handledAt", 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
    left join public.platform_users u on u.id = r.user_id
    left join public.platform_users handler on handler.id = r.handled_by
    where ${filters.join(' and ')}
    order by case r.priority
      when 'urgent' then 1
      when 'high' then 2
      when 'normal' then 3
      else 4
    end, r.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function tenantFeedbackReportRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'feedback:read');
  const range = feedbackReportRange(stringParam(ctx, 'timeRange'));
  const limit = boundedIntParam(ctx, 'limit', 20, 1, 100);
  const baseParams = [auth.tenantId, range.startAt, range.endAt];

  const [
    summary,
    byStatus,
    byType,
    byCategory,
    byPriority,
    dailyTrend,
    topQuestions,
    topHandlers,
    recentUnhandled,
  ] = await Promise.all([
    query<Record<string, unknown>>(
      `
      with created_reports as (
        select *
        from public.reports
        where tenant_id = $1
          and created_at >= $2::timestamptz
          and created_at < $3::timestamptz
      ),
      handled_reports as (
        select *
        from public.reports
        where tenant_id = $1
          and handled_at >= $2::timestamptz
          and handled_at < $3::timestamptz
          and status <> 'pending'
      ),
      feedback_rewards as (
        select e.*
        from public.user_score_events e
        join public.reports r on r.tenant_id = e.tenant_id and r.id = e.source_id
        where e.tenant_id = $1
          and e.created_at >= $2::timestamptz
          and e.created_at < $3::timestamptz
          and e.event_type = 'feedback_reward'
          and e.source_type = 'reports'
      )
      select
        (select count(*)::int from created_reports) as total,
        (select count(*)::int from created_reports where status = 'pending') as pending,
        (select count(*)::int from created_reports where status = 'accepted') as accepted,
        (select count(*)::int from created_reports where status = 'rejected') as rejected,
        (select count(*)::int from created_reports where status = 'resolved') as resolved,
        (select count(*)::int from created_reports where status = 'closed') as closed,
        (select count(*)::int from created_reports where priority in ('high', 'urgent')) as "highPriority",
        (select count(*)::int from public.reports where tenant_id = $1 and status in ('pending', 'accepted')) as "pendingBacklog",
        (select count(*)::int from handled_reports) as "handledInRange",
        (select count(distinct source_id)::int from feedback_rewards where source_id is not null) as "rewardedReports",
        (select coalesce(sum(points), 0)::int from feedback_rewards where points > 0) as "rewardPoints",
        (
          select coalesce(avg(extract(epoch from (handled_at - created_at)) / 3600), 0)::numeric
          from handled_reports
          where handled_at is not null
            and handled_at >= created_at
        ) as "avgHandleHours"
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      select status,
             case status
               when 'pending' then '待处理'
               when 'accepted' then '已受理'
               when 'rejected' then '未采纳'
               when 'resolved' then '已解决'
               when 'closed' then '已关闭'
               else status
             end as label,
             count(*)::int as count,
             count(*) filter (where priority in ('high', 'urgent'))::int as "highPriority"
      from public.reports
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      group by status
      order by case status
        when 'pending' then 1
        when 'accepted' then 2
        when 'resolved' then 3
        when 'rejected' then 4
        when 'closed' then 5
        else 9
      end
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      select coalesce(type, 'other') as type,
             count(*)::int as count,
             max(created_at) as "latestAt"
      from public.reports
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      group by coalesce(type, 'other')
      order by count desc, "latestAt" desc
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      select coalesce(nullif(category, ''), 'uncategorized') as category,
             count(*)::int as count,
             max(created_at) as "latestAt"
      from public.reports
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      group by coalesce(nullif(category, ''), 'uncategorized')
      order by count desc, "latestAt" desc
      limit $4
      `,
      [...baseParams, limit],
    ),
    query<Record<string, unknown>>(
      `
      select priority,
             count(*)::int as count
      from public.reports
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      group by priority
      order by case priority
        when 'urgent' then 1
        when 'high' then 2
        when 'normal' then 3
        when 'low' then 4
        else 9
      end
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      with days as (
        select generate_series(
          $2::timestamptz,
          $3::timestamptz - interval '1 day',
          interval '1 day'
        ) as day_start
      ),
      created_daily as (
        select (created_at at time zone 'Asia/Shanghai')::date::text as date,
               count(*)::int as total,
               count(*) filter (where status = 'pending')::int as pending,
               count(*) filter (where status = 'resolved')::int as resolved,
               count(*) filter (where priority in ('high', 'urgent'))::int as "highPriority"
        from public.reports
        where tenant_id = $1
          and created_at >= $2::timestamptz
          and created_at < $3::timestamptz
        group by (created_at at time zone 'Asia/Shanghai')::date
      ),
      handled_daily as (
        select (handled_at at time zone 'Asia/Shanghai')::date::text as date,
               count(*)::int as handled
        from public.reports
        where tenant_id = $1
          and handled_at >= $2::timestamptz
          and handled_at < $3::timestamptz
          and status <> 'pending'
        group by (handled_at at time zone 'Asia/Shanghai')::date
      )
      select (d.day_start at time zone 'Asia/Shanghai')::date::text as date,
             coalesce(cd.total, 0)::int as total,
             coalesce(cd.pending, 0)::int as pending,
             coalesce(cd.resolved, 0)::int as resolved,
             coalesce(cd."highPriority", 0)::int as "highPriority",
             coalesce(hd.handled, 0)::int as handled
      from days d
      left join created_daily cd on cd.date = (d.day_start at time zone 'Asia/Shanghai')::date::text
      left join handled_daily hd on hd.date = (d.day_start at time zone 'Asia/Shanghai')::date::text
      order by date asc
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      select r.question_id as "questionId",
             q.type as "questionType",
             left(coalesce(nullif(q.type_label, ''), nullif(v.content, ''), q.legacy_id, r.question_id::text), 120) as "questionPreview",
             count(*)::int as "reportCount",
             count(*) filter (where r.priority in ('high', 'urgent'))::int as "highPriority",
             max(r.created_at) as "latestAt"
      from public.reports r
      join public.questions q on q.tenant_id = r.tenant_id and q.id = r.question_id
      left join public.question_versions v on v.id = q.current_version_id
      where r.tenant_id = $1
        and r.question_id is not null
        and r.created_at >= $2::timestamptz
        and r.created_at < $3::timestamptz
      group by r.question_id, q.type, q.type_label, q.legacy_id, v.content
      order by "reportCount" desc, "highPriority" desc, "latestAt" desc
      limit $4
      `,
      [...baseParams, limit],
    ),
    query<Record<string, unknown>>(
      `
      select r.handled_by as "handlerUserId",
             handler.name as "handlerName",
             handler.phone as "handlerPhone",
             count(*)::int as "handledCount",
             count(*) filter (where r.status = 'resolved')::int as resolved,
             count(*) filter (where r.status = 'rejected')::int as rejected,
             count(*) filter (where r.status = 'closed')::int as closed,
             coalesce(avg(extract(epoch from (r.handled_at - r.created_at)) / 3600), 0)::numeric as "avgHandleHours",
             max(r.handled_at) as "latestHandledAt"
      from public.reports r
      left join public.platform_users handler on handler.id = r.handled_by
      where r.tenant_id = $1
        and r.handled_by is not null
        and r.handled_at >= $2::timestamptz
        and r.handled_at < $3::timestamptz
        and r.status <> 'pending'
      group by r.handled_by, handler.name, handler.phone
      order by "handledCount" desc, "latestHandledAt" desc
      limit $4
      `,
      [...baseParams, limit],
    ),
    query<Record<string, unknown>>(
      `
      select r.id, r.question_id as "questionId", q.type as "questionType",
             r.user_id as "userId", u.name as "userName", u.phone as "userPhone",
             r.type, r.category, r.title, r.status, r.priority,
             extract(epoch from (now() - r.created_at)) / 3600 as "ageHours",
             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
      left join public.platform_users u on u.id = r.user_id
      where r.tenant_id = $1
        and r.status in ('pending', 'accepted')
      order by case r.priority
        when 'urgent' then 1
        when 'high' then 2
        when 'normal' then 3
        else 4
      end, r.created_at asc
      limit $2
      `,
      [auth.tenantId, limit],
    ),
  ]);

  const summaryRow = summary[0] || {};
  const total = intValue(summaryRow.total, 0);
  const resolved = intValue(summaryRow.resolved, 0);
  const closed = intValue(summaryRow.closed, 0);
  const rejected = intValue(summaryRow.rejected, 0);
  const handledInRange = intValue(summaryRow.handledInRange, 0);
  return {
    item: {
      range,
      summary: {
        total,
        newInRange: total,
        pending: intValue(summaryRow.pending, 0),
        accepted: intValue(summaryRow.accepted, 0),
        rejected,
        resolved,
        closed,
        highPriority: intValue(summaryRow.highPriority, 0),
        pendingBacklog: intValue(summaryRow.pendingBacklog, 0),
        handledInRange,
        rewardedReports: intValue(summaryRow.rewardedReports, 0),
        rewardPoints: intValue(summaryRow.rewardPoints, 0),
        resolutionRate: ratio(resolved + closed, total),
        handledRate: ratio(handledInRange, total),
        rejectionRate: ratio(rejected, total),
        avgHandleHours: Number(numberValue(summaryRow.avgHandleHours, 0).toFixed(2)),
      },
      byStatus,
      byType,
      byCategory,
      byPriority,
      dailyTrend,
      topQuestions,
      topHandlers: topHandlers.map(item => ({
        ...item,
        avgHandleHours: Number(numberValue(item.avgHandleHours, 0).toFixed(2)),
      })),
      recentUnhandled: recentUnhandled.map(item => ({
        ...item,
        ageHours: Number(numberValue(item.ageHours, 0).toFixed(2)),
      })),
    },
  };
}

export async function tenantFeedbackEventsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'feedback:read');
  const reportId = stringParam(ctx, 'reportId');
  if (!reportId) throw new HttpError(400, 'reportId is required', 'REPORT_ID_REQUIRED');
  const items = await query(
    `
    select e.id, e.report_id as "reportId", e.from_status as "fromStatus",
           e.to_status as "toStatus", e.note, e.actor_user_id as "actorUserId",
           actor.name as "actorName", e.metadata, e.created_at as "createdAt"
    from public.report_status_events e
    left join public.platform_users actor on actor.id = e.actor_user_id
    where e.tenant_id = $1 and e.report_id = $2
    order by e.created_at asc
    `,
    [auth.tenantId, reportId],
  );
  return { items };
}

export async function updateTenantFeedbackStatusRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'feedback:write');
  const body = await readJsonBody(ctx);
  const reportId = requiredString(body, 'reportId');
  const nextStatus = optionalChoice(body.status, REPORT_STATUSES, 'accepted');
  const rewardPoints = Math.max(0, intValue(body.rewardPoints, 0));

  const item = await transaction(async client => {
    const current = await client.query<{ status: string; userId: string | null }>(
      `
      select status, user_id as "userId"
      from public.reports
      where tenant_id = $1 and id = $2
      limit 1
      `,
      [auth.tenantId, reportId],
    );
    if (!current.rows[0]) throw new HttpError(404, 'Feedback report not found', 'REPORT_NOT_FOUND');

    const result = await client.query(
      `
      update public.reports
      set status = $3,
          priority = $4,
          resolution = coalesce($5, resolution),
          handled_by = $6,
          handled_at = now(),
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, question_id as "questionId", user_id as "userId", type,
                title, description, status, priority, resolution,
                handled_by as "handledBy", handled_at as "handledAt",
                updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        reportId,
        nextStatus,
        optionalChoice(body.priority, REPORT_PRIORITIES, 'normal'),
        nullableString(body.resolution),
        auth.userId,
      ],
    );

    await client.query(
      `
      insert into public.report_status_events (
        tenant_id, report_id, from_status, to_status, note, actor_user_id, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7::jsonb)
      `,
      [
        auth.tenantId,
        reportId,
        current.rows[0].status,
        nextStatus,
        nullableString(body.note),
        auth.userId,
        JSON.stringify(objectValue(body.metadata)),
      ],
    );

    let reward = null;
    let autoBadges: AutoBadgeGrant[] = [];
    if (rewardPoints > 0 && current.rows[0].userId) {
      const currentScore = await client.query<{ score: number }>(
        'select score from public.platform_users where id = $1 for update',
        [current.rows[0].userId],
      );
      const balanceAfter = Number(currentScore.rows[0]?.score || 0) + rewardPoints;
      const ledger = await client.query(
        `
        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, 'feedback_reward', $3, $4, 'reports', $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"
        `,
        [
          auth.tenantId,
          current.rows[0].userId,
          rewardPoints,
          balanceAfter,
          reportId,
          `feedback_reward:${reportId}`,
          JSON.stringify({ reportId, status: nextStatus }),
        ],
      );
      if (ledger.rows[0]) {
        const updatedUser = await client.query<{ score: number }>(
          `
          update public.platform_users
          set score = score + $2,
              updated_at = now()
          where id = $1
          returning score
          `,
          [current.rows[0].userId, rewardPoints],
        );
        reward = { ...ledger.rows[0], balanceAfter: updatedUser.rows[0]?.score || ledger.rows[0].balanceAfter };
        autoBadges = [
          ...autoBadges,
          ...(await autoGrantBadges(client, {
            tenantId: auth.tenantId,
            userId: current.rows[0].userId,
            trigger: 'score',
            evidence: {
              source: 'feedback_reward',
              reportId,
              rewardPoints,
              score: reward.balanceAfter,
            },
          })),
        ];
      }
    }

    if (nextStatus === 'resolved' && current.rows[0].status !== 'resolved' && current.rows[0].userId) {
      const feedbackStats = await client.query<{ resolvedCount: string }>(
        `
        select count(*)::text as "resolvedCount"
        from public.reports
        where tenant_id = $1
          and user_id = $2
          and status = 'resolved'
        `,
        [auth.tenantId, current.rows[0].userId],
      );

      autoBadges = [
        ...autoBadges,
        ...(await autoGrantBadges(client, {
          tenantId: auth.tenantId,
          userId: current.rows[0].userId,
          trigger: 'feedback_resolved',
          evidence: {
            reportId,
            feedbackResolvedCount: Number(feedbackStats.rows[0]?.resolvedCount || 0),
            rewardPoints,
            status: nextStatus,
          },
        })),
      ];
    }

    if (current.rows[0].userId) {
      const statusLabel = REPORT_STATUS_LABELS[nextStatus] || nextStatus;
      const resolution = nullableString(body.resolution);
      await createUserNotification(client, {
        tenantId: auth.tenantId,
        userId: current.rows[0].userId,
        notificationType: 'feedback_status_updated',
        severity: feedbackNotificationSeverity(nextStatus),
        title: `反馈${statusLabel}`,
        message: resolution || `你的反馈已更新为「${statusLabel}」。`,
        actionLabel: '查看反馈',
        actionPath: '/student/profile?tab=feedbacks',
        sourceType: 'reports',
        sourceId: reportId,
        dedupeKey: `feedback_status:${reportId}:${nextStatus}`,
        metadata: {
          reportId,
          fromStatus: current.rows[0].status,
          toStatus: nextStatus,
          rewardPoints,
          autoBadgeCount: autoBadges.length,
        },
        createdBy: auth.userId,
      });

      if (reward) {
        await createUserNotification(client, {
          tenantId: auth.tenantId,
          userId: current.rows[0].userId,
          notificationType: 'feedback_reward_granted',
          severity: 'success',
          title: '反馈奖励已发放',
          message: `感谢你的反馈，本次已奖励 ${rewardPoints} 积分。`,
          actionLabel: '查看积分',
          actionPath: '/student/profile?tab=points',
          sourceType: 'reports',
          sourceId: reportId,
          dedupeKey: `feedback_reward:${reportId}`,
          metadata: {
            reportId,
            rewardPoints,
            scoreEventId: reward.id,
            balanceAfter: reward.balanceAfter,
          },
          createdBy: auth.userId,
        });
      }
    }

    await recordAudit(client, auth, 'tenant.feedback.status_updated', 'reports', reportId, {
      fromStatus: current.rows[0].status,
      toStatus: nextStatus,
      rewardPoints,
      autoBadgeCount: autoBadges.length,
    });
    return { ...result.rows[0], reward, autoBadges };
  });

  return { item };
}
