import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, stringParam } from '../../core/request.js';
import { query, queryOne } from '../../core/db.js';
import { requireTenantAdmin, requireTenantPermission } from './auth.js';

const DASHBOARD_RANGES: Record<string, number> = {
  '7d': 7,
  '30d': 30,
  '90d': 90,
};

const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

const shanghaiDateFormatter = new Intl.DateTimeFormat('en-US', {
  timeZone: 'Asia/Shanghai',
  year: 'numeric',
  month: '2-digit',
  day: '2-digit',
});

type NumericRow = Record<string, unknown>;

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

function intValue(value: unknown) {
  return Math.trunc(numberValue(value));
}

function moneyValue(value: unknown) {
  return Math.trunc(numberValue(value));
}

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

function shanghaiDateKey(date: Date) {
  const parts = Object.fromEntries(
    shanghaiDateFormatter.formatToParts(date).map(part => [part.type, part.value]),
  );
  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 parseRange(value: string) {
  const timeRange = value || '30d';
  const days = DASHBOARD_RANGES[timeRange];
  if (!days) throw new HttpError(400, 'Unsupported dashboard time range', 'INVALID_DASHBOARD_RANGE');

  const endDate = shanghaiDateKey(new Date());
  const startDate = addDaysKey(endDate, -(days - 1));
  const endExclusiveDate = addDaysKey(endDate, 1);
  return {
    timeRange,
    days,
    startDate,
    endDate,
    startAt: shanghaiDayStartIso(startDate),
    endAt: shanghaiDayStartIso(endExclusiveDate),
  };
}

function emptyTrendSeries(startDate: string, days: number) {
  return Array.from({ length: days }, (_, index) => ({
    date: addDaysKey(startDate, index),
    newStudents: 0,
    orderCount: 0,
    paidOrderCount: 0,
    revenueCents: 0,
    answers: 0,
    correctAnswers: 0,
    activeStudents: 0,
  }));
}

function applyTrendRows(
  trends: ReturnType<typeof emptyTrendSeries>,
  rows: NumericRow[],
  mapper: (target: ReturnType<typeof emptyTrendSeries>[number], row: NumericRow) => void,
) {
  const trendMap = new Map(trends.map(item => [item.date, item]));
  for (const row of rows) {
    const target = trendMap.get(String(row.date || ''));
    if (target) mapper(target, row);
  }
}

async function ensureRegionInTenant(tenantId: string, regionId: string | null) {
  if (!regionId) return null;
  if (!UUID_RE.test(regionId)) {
    throw new HttpError(400, 'Invalid regionId', 'INVALID_REGION_ID');
  }

  const region = await queryOne<{ id: string; name: string }>(
    `
    select id, name
    from public.regions
    where tenant_id = $1 and id = $2 and is_active = true
    limit 1
    `,
    [tenantId, regionId],
  );
  if (!region) throw new HttpError(404, 'Region not found for this tenant', 'REGION_NOT_FOUND');
  return region;
}

export async function tenantDashboardRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'dashboard:read');

  const range = parseRange(stringParam(ctx, 'timeRange'));
  const rawRegionId = stringParam(ctx, 'regionId') || null;
  const region = await ensureRegionInTenant(auth.tenantId, rawRegionId);
  const regionId = region?.id || null;
  const limit = intParam(ctx, 'limit', 10, 50);
  const baseParams = [auth.tenantId, range.startAt, range.endAt, regionId];
  const listParams = [...baseParams, limit];
  const scopedParams = [auth.tenantId, regionId];
  const scopedListParams = [auth.tenantId, regionId, limit];

  const [
    studentTotals,
    orderTotals,
    contentTotals,
    learningTotals,
    codeTotals,
    feedbackTotals,
    studentTrend,
    orderTrend,
    revenueTrend,
    answerTrend,
    activeHoursRows,
    questionDistributionRows,
    subjectTopRows,
    regionStatsRows,
    planSalesRows,
    recentActivitiesRows,
  ] = await Promise.all([
    queryOne<NumericRow>(
      `
      select
        count(distinct tm.user_id)::integer as "totalStudents",
        count(distinct tm.user_id) filter (
          where tm.created_at >= $2::timestamptz and tm.created_at < $3::timestamptz
        )::integer as "newStudents",
        count(distinct sp.user_id) filter (
          where greatest(coalesce(sp.updated_at, '-infinity'::timestamptz), coalesce(u.last_seen_at, '-infinity'::timestamptz)) >= now() - interval '7 days'
        )::integer as "activeStudents7d",
        count(distinct e.user_id) filter (where e.entitlement_type = 'svip')::integer as "svipStudents",
        count(distinct e.user_id) filter (where e.entitlement_type = 'video_quota')::integer as "videoMembers"
      from public.tenant_memberships tm
      join public.platform_users u on u.id = tm.user_id
      left join public.student_profiles sp on sp.tenant_id = tm.tenant_id and sp.user_id = tm.user_id
      left join public.entitlements e
        on e.tenant_id = tm.tenant_id
       and e.user_id = tm.user_id
       and e.status = 'active'
       and (e.expires_at is null or e.expires_at > now())
      where tm.tenant_id = $1
        and tm.role = 'student'
        and tm.status = 'active'
        and ($4::uuid is null or sp.region_id = $4::uuid)
      `,
      baseParams,
    ),
    queryOne<NumericRow>(
      `
      select
        count(*)::integer as "totalOrders",
        count(*) filter (where o.created_at >= $2::timestamptz and o.created_at < $3::timestamptz)::integer as "ordersInRange",
        count(*) filter (where o.status = 'paid')::integer as "paidOrders",
        count(*) filter (
          where o.status = 'paid'
            and coalesce(o.paid_at, o.updated_at, o.created_at) >= $2::timestamptz
            and coalesce(o.paid_at, o.updated_at, o.created_at) < $3::timestamptz
        )::integer as "paidOrdersInRange",
        count(*) filter (where o.status = 'pending')::integer as "pendingOrders",
        count(*) filter (where o.status in ('failed', 'closed'))::integer as "failedOrders",
        count(*) filter (where o.status = 'refunded')::integer as "refundedOrders",
        coalesce(sum(o.amount_cents) filter (where o.status = 'paid'), 0)::bigint as "revenueCents",
        coalesce(sum(o.amount_cents) filter (
          where o.status = 'paid'
            and coalesce(o.paid_at, o.updated_at, o.created_at) >= $2::timestamptz
            and coalesce(o.paid_at, o.updated_at, o.created_at) < $3::timestamptz
        ), 0)::bigint as "revenueCentsInRange",
        coalesce(avg(o.amount_cents) filter (where o.status = 'paid'), 0)::numeric as "averagePaidAmountCents"
      from public.orders o
      where o.tenant_id = $1
        and ($4::uuid is null or o.region_id = $4::uuid)
      `,
      baseParams,
    ),
    queryOne<NumericRow>(
      `
      select
        (select count(*)::integer from public.regions r
          where r.tenant_id = $1 and r.is_active = true and ($2::uuid is null or r.id = $2::uuid)) as regions,
        (select count(*)::integer from public.subjects s
          where s.tenant_id = $1 and s.is_active = true and ($2::uuid is null or s.region_id = $2::uuid)) as subjects,
        (select count(*)::integer from public.content_entries ce
          where ce.tenant_id = $1 and ce.is_active = true and ($2::uuid is null or ce.region_id = $2::uuid or ce.region_id is null)) as "contentEntries",
        (select count(*)::integer from public.content_nodes cn
          where cn.tenant_id = $1 and cn.is_active = true and ($2::uuid is null or cn.region_id = $2::uuid or cn.region_id is null)) as "contentNodes",
        (select count(*)::integer from public.question_banks qb
          where qb.tenant_id = $1 and qb.status = 'active' and ($2::uuid is null or qb.region_id = $2::uuid)) as "questionBanks",
        (select count(*)::integer from public.question_collections qc
          left join public.content_entries ce on ce.tenant_id = qc.tenant_id and ce.id = qc.entry_id
          left join public.content_nodes cn on cn.tenant_id = qc.tenant_id and cn.id = qc.node_id
          where qc.tenant_id = $1 and qc.status = 'active'
            and ($2::uuid is null or coalesce(qc.region_id, cn.region_id, ce.region_id) = $2::uuid)) as "questionCollections",
        (select count(*)::integer from public.practice_blueprints pb
          left join public.content_entries ce on ce.tenant_id = pb.tenant_id and ce.id = pb.entry_id
          left join public.content_nodes cn on cn.tenant_id = pb.tenant_id and cn.id = pb.node_id
          where pb.tenant_id = $1 and pb.status = 'active'
            and ($2::uuid is null or coalesce(pb.region_id, cn.region_id, ce.region_id) = $2::uuid)) as "practiceBlueprints",
        (select count(distinct q.id)::integer
          from public.questions q
          left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
          left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
          left join public.content_entries ce on ce.tenant_id = q.tenant_id and ce.id = q.entry_id
          where q.tenant_id = $1 and q.status = 'published'
            and ($2::uuid is null or coalesce(qb.region_id, s.region_id, ce.region_id) = $2::uuid)) as questions,
        (select count(distinct qv.id)::integer
          from public.question_videos qv
          join public.questions q on q.tenant_id = qv.tenant_id and q.id = qv.question_id
          left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
          left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
          where qv.tenant_id = $1
            and ($2::uuid is null or coalesce(qb.region_id, s.region_id) = $2::uuid)) as "questionVideos",
        (select count(*)::integer from public.content_assets ca
          where ca.tenant_id = $1 and ca.status = 'active'
            and ($2::uuid is null or ca.region_id = $2::uuid or ca.region_id is null)) as assets,
        (select count(*)::integer from public.vocabulary_units vu
          where vu.tenant_id = $1 and vu.is_active = true and ($2::uuid is null or vu.region_id = $2::uuid)) as "vocabularyUnits",
        (select count(*)::integer
          from public.vocabulary_words vw
          left join public.vocabulary_units vu on vu.tenant_id = vw.tenant_id and vu.id = vw.unit_id
          where vw.tenant_id = $1 and vw.is_active = true
            and ($2::uuid is null or vu.region_id = $2::uuid)) as "vocabularyWords",
        (select count(*)::integer from public.handbook_subjects hs
          where hs.tenant_id = $1 and hs.is_active = true and ($2::uuid is null or hs.region_id = $2::uuid)) as "handbookSubjects",
        (select count(*)::integer
          from public.handbook_entries he
          join public.handbook_chapters hc on hc.tenant_id = he.tenant_id and hc.id = he.chapter_id
          join public.handbook_subjects hs on hs.tenant_id = he.tenant_id and hs.id = hc.subject_id
          where he.tenant_id = $1 and he.is_active = true and hc.is_active = true and hs.is_active = true
            and ($2::uuid is null or hs.region_id = $2::uuid)) as "handbookEntries"
      `,
      scopedParams,
    ),
    queryOne<NumericRow>(
      `
      with scoped_answers as (
        select ar.*
        from public.answer_records ar
        left join public.questions q on q.tenant_id = ar.tenant_id and q.id = ar.question_id
        left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
        left join public.student_profiles sp on sp.tenant_id = ar.tenant_id and sp.user_id = ar.user_id
        where ar.tenant_id = $1
          and ar.answered_at >= $2::timestamptz
          and ar.answered_at < $3::timestamptz
          and ($4::uuid is null or coalesce(qb.region_id, s.region_id, sp.region_id) = $4::uuid)
      ),
      scoped_reports as (
        select psr.*
        from public.practice_session_reports psr
        left join public.student_profiles sp on sp.tenant_id = psr.tenant_id and sp.user_id = psr.user_id
        where psr.tenant_id = $1
          and psr.submitted_at >= $2::timestamptz
          and psr.submitted_at < $3::timestamptz
          and ($4::uuid is null or sp.region_id = $4::uuid)
      )
      select
        (select count(*)::integer from scoped_answers) as answers,
        (select count(*)::integer from scoped_answers where is_correct is true) as "correctAnswers",
        (select count(*)::integer from scoped_answers where is_correct is false) as "wrongAnswers",
        (select count(distinct user_id)::integer from scoped_answers) as "activeStudents",
        (select count(distinct practice_session_id)::integer from scoped_answers where practice_session_id is not null) as "activeSessions",
        (select count(*)::integer from scoped_reports) as reports,
        (select coalesce(avg(score), 0)::numeric from scoped_reports) as "averageScore",
        (select coalesce(avg(accuracy), 0)::numeric from scoped_reports) as "averageAccuracy"
      `,
      baseParams,
    ),
    queryOne<NumericRow>(
      `
      with scoped_codes as (
        select ac.*, coalesce(ac.used_region_id, cb.region_id) as scope_region_id
        from public.activation_codes ac
        left join public.code_batches cb on cb.tenant_id = ac.tenant_id and cb.id = ac.batch_id
        where ac.tenant_id = $1
          and ($4::uuid is null or coalesce(ac.used_region_id, cb.region_id) = $4::uuid)
      )
      select
        count(*)::integer as total,
        count(*) filter (where is_used is true)::integer as used,
        count(*) filter (where is_used is false)::integer as unused,
        count(*) filter (
          where created_at >= $2::timestamptz and created_at < $3::timestamptz
        )::integer as "generatedInRange",
        count(*) filter (
          where is_used is true and used_at >= $2::timestamptz and used_at < $3::timestamptz
        )::integer as "usedInRange",
        coalesce(sum(unit_price_cents) filter (where is_used is true), 0)::bigint as "estimatedRevenueCents"
      from scoped_codes
      `,
      baseParams,
    ),
    queryOne<NumericRow>(
      `
      with scoped_reports as (
        select r.*
        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.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
        left join public.student_profiles sp on sp.tenant_id = r.tenant_id and sp.user_id = r.user_id
        where r.tenant_id = $1
          and ($4::uuid is null or coalesce(qb.region_id, s.region_id, sp.region_id) = $4::uuid)
      )
      select
        count(*)::integer as total,
        count(*) filter (where created_at >= $2::timestamptz and created_at < $3::timestamptz)::integer as "newInRange",
        count(*) filter (where status = 'pending')::integer as pending,
        count(*) filter (where status in ('accepted', 'resolved'))::integer as handled,
        count(*) filter (where status in ('resolved', 'closed'))::integer as resolved,
        count(*) filter (where priority in ('high', 'urgent'))::integer as "highPriority"
      from scoped_reports
      `,
      baseParams,
    ),
    query<NumericRow>(
      `
      select to_char(timezone('Asia/Shanghai', tm.created_at), 'YYYY-MM-DD') as date,
             count(distinct tm.user_id)::integer as "newStudents"
      from public.tenant_memberships tm
      left join public.student_profiles sp on sp.tenant_id = tm.tenant_id and sp.user_id = tm.user_id
      where tm.tenant_id = $1
        and tm.role = 'student'
        and tm.status = 'active'
        and tm.created_at >= $2::timestamptz
        and tm.created_at < $3::timestamptz
        and ($4::uuid is null or sp.region_id = $4::uuid)
      group by date
      order by date asc
      `,
      baseParams,
    ),
    query<NumericRow>(
      `
      select to_char(timezone('Asia/Shanghai', o.created_at), 'YYYY-MM-DD') as date,
             count(*)::integer as "orderCount"
      from public.orders o
      where o.tenant_id = $1
        and o.created_at >= $2::timestamptz
        and o.created_at < $3::timestamptz
        and ($4::uuid is null or o.region_id = $4::uuid)
      group by date
      order by date asc
      `,
      baseParams,
    ),
    query<NumericRow>(
      `
      select to_char(timezone('Asia/Shanghai', coalesce(o.paid_at, o.updated_at, o.created_at)), 'YYYY-MM-DD') as date,
             count(*)::integer as "paidOrderCount",
             coalesce(sum(o.amount_cents), 0)::bigint as "revenueCents"
      from public.orders o
      where o.tenant_id = $1
        and o.status = 'paid'
        and coalesce(o.paid_at, o.updated_at, o.created_at) >= $2::timestamptz
        and coalesce(o.paid_at, o.updated_at, o.created_at) < $3::timestamptz
        and ($4::uuid is null or o.region_id = $4::uuid)
      group by date
      order by date asc
      `,
      baseParams,
    ),
    query<NumericRow>(
      `
      select to_char(timezone('Asia/Shanghai', ar.answered_at), 'YYYY-MM-DD') as date,
             count(*)::integer as answers,
             count(*) filter (where ar.is_correct is true)::integer as "correctAnswers",
             count(distinct ar.user_id)::integer as "activeStudents"
      from public.answer_records ar
      left join public.questions q on q.tenant_id = ar.tenant_id and q.id = ar.question_id
      left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
      left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
      left join public.student_profiles sp on sp.tenant_id = ar.tenant_id and sp.user_id = ar.user_id
      where ar.tenant_id = $1
        and ar.answered_at >= $2::timestamptz
        and ar.answered_at < $3::timestamptz
        and ($4::uuid is null or coalesce(qb.region_id, s.region_id, sp.region_id) = $4::uuid)
      group by date
      order by date asc
      `,
      baseParams,
    ),
    query<NumericRow>(
      `
      select extract(hour from timezone('Asia/Shanghai', ar.answered_at))::integer as hour,
             count(*)::integer as answers,
             count(distinct ar.user_id)::integer as "activeStudents"
      from public.answer_records ar
      left join public.questions q on q.tenant_id = ar.tenant_id and q.id = ar.question_id
      left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
      left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
      left join public.student_profiles sp on sp.tenant_id = ar.tenant_id and sp.user_id = ar.user_id
      where ar.tenant_id = $1
        and ar.answered_at >= $2::timestamptz
        and ar.answered_at < $3::timestamptz
        and ($4::uuid is null or coalesce(qb.region_id, s.region_id, sp.region_id) = $4::uuid)
      group by hour
      order by hour asc
      `,
      baseParams,
    ),
    query<NumericRow>(
      `
      select coalesce(q.type, 'unknown') as type,
             coalesce(max(q.type_label), q.type, '未知题型') as label,
             count(*)::integer as "questionCount"
      from public.questions q
      left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
      left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
      left join public.content_entries ce on ce.tenant_id = q.tenant_id and ce.id = q.entry_id
      where q.tenant_id = $1
        and q.status = 'published'
        and ($2::uuid is null or coalesce(qb.region_id, s.region_id, ce.region_id) = $2::uuid)
      group by q.type
      order by "questionCount" desc, type asc
      limit $3
      `,
      scopedListParams,
    ),
    query<NumericRow>(
      `
      with question_counts as (
        select q.subject_id, count(*)::integer as question_count
        from public.questions q
        left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        where q.tenant_id = $1 and q.status = 'published'
          and ($4::uuid is null or qb.region_id = $4::uuid or q.subject_id in (
            select id from public.subjects where tenant_id = $1 and region_id = $4::uuid
          ))
        group by q.subject_id
      ),
      answer_counts as (
        select q.subject_id,
               count(ar.id)::integer as answer_count,
               count(ar.id) filter (where ar.is_correct is true)::integer as correct_count
        from public.answer_records ar
        join public.questions q on q.tenant_id = ar.tenant_id and q.id = ar.question_id
        left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        where ar.tenant_id = $1
          and ar.answered_at >= $2::timestamptz
          and ar.answered_at < $3::timestamptz
          and ($4::uuid is null or qb.region_id = $4::uuid or q.subject_id in (
            select id from public.subjects where tenant_id = $1 and region_id = $4::uuid
          ))
        group by q.subject_id
      )
      select s.id, s.name, s.type,
             coalesce(qc.question_count, 0)::integer as "questionCount",
             coalesce(ac.answer_count, 0)::integer as "answerCount",
             coalesce(ac.correct_count, 0)::integer as "correctCount"
      from public.subjects s
      left join question_counts qc on qc.subject_id = s.id
      left join answer_counts ac on ac.subject_id = s.id
      where s.tenant_id = $1
        and s.is_active = true
        and ($4::uuid is null or s.region_id = $4::uuid)
      order by coalesce(qc.question_count, 0) desc, coalesce(ac.answer_count, 0) desc, s.sort_order asc
      limit $5
      `,
      listParams,
    ),
    query<NumericRow>(
      `
      with region_base as (
        select r.id, r.name, r.code, r.sort_order
        from public.regions r
        where r.tenant_id = $1
          and r.is_active = true
          and ($4::uuid is null or r.id = $4::uuid)
      ),
      students as (
        select sp.region_id, count(distinct sp.user_id)::integer as student_count
        from public.student_profiles sp
        join public.tenant_memberships tm
          on tm.tenant_id = sp.tenant_id and tm.user_id = sp.user_id and tm.role = 'student' and tm.status = 'active'
        where sp.tenant_id = $1 and sp.region_id is not null
        group by sp.region_id
      ),
      questions as (
        select coalesce(qb.region_id, s.region_id) as region_id, count(distinct q.id)::integer as question_count
        from public.questions q
        left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
        where q.tenant_id = $1 and q.status = 'published'
        group by coalesce(qb.region_id, s.region_id)
      ),
      answers as (
        select coalesce(qb.region_id, s.region_id, sp.region_id) as region_id,
               count(ar.id)::integer as answer_count,
               count(distinct ar.user_id)::integer as active_students
        from public.answer_records ar
        left join public.questions q on q.tenant_id = ar.tenant_id and q.id = ar.question_id
        left join public.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
        left join public.student_profiles sp on sp.tenant_id = ar.tenant_id and sp.user_id = ar.user_id
        where ar.tenant_id = $1
          and ar.answered_at >= $2::timestamptz
          and ar.answered_at < $3::timestamptz
        group by coalesce(qb.region_id, s.region_id, sp.region_id)
      ),
      revenue as (
        select o.region_id,
               count(*) filter (where o.status = 'paid')::integer as paid_orders,
               coalesce(sum(o.amount_cents) filter (where o.status = 'paid'), 0)::bigint as revenue_cents
        from public.orders o
        where o.tenant_id = $1
          and o.region_id is not null
          and coalesce(o.paid_at, o.updated_at, o.created_at) >= $2::timestamptz
          and coalesce(o.paid_at, o.updated_at, o.created_at) < $3::timestamptz
        group by o.region_id
      )
      select rb.id, rb.name, rb.code,
             coalesce(st.student_count, 0)::integer as "studentCount",
             coalesce(q.question_count, 0)::integer as "questionCount",
             coalesce(a.answer_count, 0)::integer as "answerCount",
             coalesce(a.active_students, 0)::integer as "activeStudents",
             coalesce(r.paid_orders, 0)::integer as "paidOrders",
             coalesce(r.revenue_cents, 0)::bigint as "revenueCents"
      from region_base rb
      left join students st on st.region_id = rb.id
      left join questions q on q.region_id = rb.id
      left join answers a on a.region_id = rb.id
      left join revenue r on r.region_id = rb.id
      order by rb.sort_order asc, rb.name asc
      limit $5
      `,
      listParams,
    ),
    query<NumericRow>(
      `
      select o.plan_id as "planId",
             coalesce(p.name, o.product_name, '未归类套餐') as name,
             count(*)::integer as "paidOrders",
             coalesce(sum(o.amount_cents), 0)::bigint as "revenueCents"
      from public.orders o
      left join public.svip_plans p on p.tenant_id = o.tenant_id and p.id = o.plan_id
      where o.tenant_id = $1
        and o.status = 'paid'
        and coalesce(o.paid_at, o.updated_at, o.created_at) >= $2::timestamptz
        and coalesce(o.paid_at, o.updated_at, o.created_at) < $3::timestamptz
        and ($4::uuid is null or o.region_id = $4::uuid)
      group by o.plan_id, p.name, o.product_name
      order by "revenueCents" desc, "paidOrders" desc
      limit $5
      `,
      listParams,
    ),
    query<NumericRow>(
      `
      select activity_type as "activityType", title, occurred_at as "occurredAt", details
      from (
        select 'student_registered'::text as activity_type,
               '新学生注册'::text as title,
               tm.created_at as occurred_at,
               jsonb_build_object('userId', tm.user_id, 'name', coalesce(u.name, u.username, '学生')) as details
        from public.tenant_memberships tm
        join public.platform_users u on u.id = tm.user_id
        left join public.student_profiles sp on sp.tenant_id = tm.tenant_id and sp.user_id = tm.user_id
        where tm.tenant_id = $1
          and tm.role = 'student'
          and tm.status = 'active'
          and tm.created_at >= $2::timestamptz
          and tm.created_at < $3::timestamptz
          and ($4::uuid is null or sp.region_id = $4::uuid)
        union all
        select 'order_paid'::text as activity_type,
               coalesce(o.product_name, '订单支付') as title,
               coalesce(o.paid_at, o.updated_at, o.created_at) as occurred_at,
               jsonb_build_object('orderNo', o.order_no, 'status', o.status, 'amountCents', o.amount_cents) as details
        from public.orders o
        where o.tenant_id = $1
          and o.status = 'paid'
          and coalesce(o.paid_at, o.updated_at, o.created_at) >= $2::timestamptz
          and coalesce(o.paid_at, o.updated_at, o.created_at) < $3::timestamptz
          and ($4::uuid is null or o.region_id = $4::uuid)
        union all
        select 'feedback_created'::text as activity_type,
               coalesce(r.title, '学生反馈') as title,
               r.created_at as occurred_at,
               jsonb_build_object('reportId', r.id, 'status', r.status, 'type', r.type, 'priority', r.priority) as details
        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.question_banks qb on qb.tenant_id = q.tenant_id and qb.id = q.question_bank_id
        left join public.subjects s on s.tenant_id = q.tenant_id and s.id = q.subject_id
        left join public.student_profiles sp on sp.tenant_id = r.tenant_id and sp.user_id = r.user_id
        where r.tenant_id = $1
          and r.created_at >= $2::timestamptz
          and r.created_at < $3::timestamptz
          and ($4::uuid is null or coalesce(qb.region_id, s.region_id, sp.region_id) = $4::uuid)
        union all
        select 'content_import'::text as activity_type,
               coalesce(cij.source_name, cij.import_type || ' 导入') as title,
               cij.created_at as occurred_at,
               jsonb_build_object('jobId', cij.id, 'importType', cij.import_type, 'status', cij.status, 'totalCount', cij.total_count) as details
        from public.content_import_jobs cij
        where cij.tenant_id = $1
          and cij.created_at >= $2::timestamptz
          and cij.created_at < $3::timestamptz
          and ($4::uuid is null or cij.target_region_id = $4::uuid or cij.target_region_id is null)
      ) activities
      order by occurred_at desc
      limit $5
      `,
      listParams,
    ),
  ]);

  const students = studentTotals || {};
  const orders = orderTotals || {};
  const content = contentTotals || {};
  const learning = learningTotals || {};
  const codes = codeTotals || {};
  const feedback = feedbackTotals || {};

  const answers = intValue(learning.answers);
  const correctAnswers = intValue(learning.correctAnswers);

  const trends = emptyTrendSeries(range.startDate, range.days);
  applyTrendRows(trends, studentTrend, (target, row) => {
    target.newStudents = intValue(row.newStudents);
  });
  applyTrendRows(trends, orderTrend, (target, row) => {
    target.orderCount = intValue(row.orderCount);
  });
  applyTrendRows(trends, revenueTrend, (target, row) => {
    target.paidOrderCount = intValue(row.paidOrderCount);
    target.revenueCents = moneyValue(row.revenueCents);
  });
  applyTrendRows(trends, answerTrend, (target, row) => {
    target.answers = intValue(row.answers);
    target.correctAnswers = intValue(row.correctAnswers);
    target.activeStudents = intValue(row.activeStudents);
  });

  const activeHourMap = new Map(activeHoursRows.map(row => [intValue(row.hour), row]));
  const activeHours = Array.from({ length: 24 }, (_, hour) => {
    const row = activeHourMap.get(hour) || {};
    return {
      hour,
      answers: intValue(row.answers),
      activeStudents: intValue(row.activeStudents),
    };
  });

  return {
    item: {
      scope: {
        tenantId: auth.tenantId,
        regionId,
        regionName: region?.name || null,
        timeRange: range.timeRange,
        days: range.days,
        startDate: range.startDate,
        endDate: range.endDate,
        timezone: 'Asia/Shanghai',
      },
      cards: {
        students: {
          total: intValue(students.totalStudents),
          newInRange: intValue(students.newStudents),
          active7d: intValue(students.activeStudents7d),
          svip: intValue(students.svipStudents),
          videoMembers: intValue(students.videoMembers),
        },
        learning: {
          answers,
          correctAnswers,
          wrongAnswers: intValue(learning.wrongAnswers),
          accuracy: ratio(correctAnswers, answers),
          activeStudents: intValue(learning.activeStudents),
          activeSessions: intValue(learning.activeSessions),
          reports: intValue(learning.reports),
          averageScore: Number(numberValue(learning.averageScore).toFixed(2)),
          averageAccuracy: Number(numberValue(learning.averageAccuracy).toFixed(4)),
        },
        content: {
          regions: intValue(content.regions),
          subjects: intValue(content.subjects),
          contentEntries: intValue(content.contentEntries),
          contentNodes: intValue(content.contentNodes),
          questionBanks: intValue(content.questionBanks),
          questionCollections: intValue(content.questionCollections),
          practiceBlueprints: intValue(content.practiceBlueprints),
          questions: intValue(content.questions),
          questionVideos: intValue(content.questionVideos),
          assets: intValue(content.assets),
          vocabularyUnits: intValue(content.vocabularyUnits),
          vocabularyWords: intValue(content.vocabularyWords),
          handbookSubjects: intValue(content.handbookSubjects),
          handbookEntries: intValue(content.handbookEntries),
        },
        activationCodes: {
          total: intValue(codes.total),
          used: intValue(codes.used),
          unused: intValue(codes.unused),
          generatedInRange: intValue(codes.generatedInRange),
          usedInRange: intValue(codes.usedInRange),
          estimatedRevenueCents: moneyValue(codes.estimatedRevenueCents),
          usageRate: ratio(intValue(codes.used), intValue(codes.total)),
        },
        feedback: {
          total: intValue(feedback.total),
          newInRange: intValue(feedback.newInRange),
          pending: intValue(feedback.pending),
          handled: intValue(feedback.handled),
          resolved: intValue(feedback.resolved),
          highPriority: intValue(feedback.highPriority),
        },
      },
      paymentStats: {
        totalOrders: intValue(orders.totalOrders),
        ordersInRange: intValue(orders.ordersInRange),
        paidOrders: intValue(orders.paidOrders),
        paidOrdersInRange: intValue(orders.paidOrdersInRange),
        pendingOrders: intValue(orders.pendingOrders),
        failedOrders: intValue(orders.failedOrders),
        refundedOrders: intValue(orders.refundedOrders),
        revenueCents: moneyValue(orders.revenueCents),
        revenueCentsInRange: moneyValue(orders.revenueCentsInRange),
        averagePaidAmountCents: moneyValue(orders.averagePaidAmountCents),
      },
      trends,
      activeHours,
      questionDistribution: questionDistributionRows.map(row => ({
        type: String(row.type || 'unknown'),
        label: String(row.label || row.type || '未知题型'),
        questionCount: intValue(row.questionCount),
      })),
      subjectTop: subjectTopRows.map(row => ({
        id: String(row.id),
        name: String(row.name),
        type: row.type ? String(row.type) : null,
        questionCount: intValue(row.questionCount),
        answerCount: intValue(row.answerCount),
        correctCount: intValue(row.correctCount),
        accuracy: ratio(intValue(row.correctCount), intValue(row.answerCount)),
      })),
      regionStats: regionStatsRows.map(row => ({
        id: String(row.id),
        name: String(row.name),
        code: row.code ? String(row.code) : null,
        studentCount: intValue(row.studentCount),
        questionCount: intValue(row.questionCount),
        answerCount: intValue(row.answerCount),
        activeStudents: intValue(row.activeStudents),
        paidOrders: intValue(row.paidOrders),
        revenueCents: moneyValue(row.revenueCents),
      })),
      planSales: planSalesRows.map(row => ({
        planId: row.planId ? String(row.planId) : null,
        name: String(row.name || '未归类套餐'),
        paidOrders: intValue(row.paidOrders),
        revenueCents: moneyValue(row.revenueCents),
      })),
      recentActivities: recentActivitiesRows.map(row => ({
        activityType: String(row.activityType),
        title: String(row.title || ''),
        occurredAt: row.occurredAt instanceof Date ? row.occurredAt.toISOString() : String(row.occurredAt),
        details: row.details || {},
      })),
      generatedAt: new Date().toISOString(),
    },
  };
}
