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

type LeaderboardMetric = 'questions' | 'score' | 'vocabulary' | 'mock_exam';
type LeaderboardPeriod = 'all' | '7d' | '30d';

interface LeaderboardRow {
  userId: string;
  username: string | null;
  name: string | null;
  avatarPreset: 'male' | 'female';
  primaryRole: string;
  regionId: string | null;
  regionName: string | null;
  classIds: string[];
  classNames: string[];
  value: string | number;
  secondaryValue: string | number | null;
  latestAt: string | null;
}

interface RankedRow extends LeaderboardRow {
  rank: string | number;
}

const METRICS = new Set<LeaderboardMetric>(['questions', 'score', 'vocabulary', 'mock_exam']);
const PERIODS = new Set<LeaderboardPeriod>(['all', '7d', '30d']);

function metricFrom(value: string): LeaderboardMetric {
  const candidate = value || 'questions';
  if (!METRICS.has(candidate as LeaderboardMetric)) {
    throw new HttpError(400, `Unsupported leaderboard metric: ${candidate}`, 'INVALID_LEADERBOARD_METRIC');
  }
  return candidate as LeaderboardMetric;
}

function periodFrom(value: string): LeaderboardPeriod {
  const candidate = value || 'all';
  if (!PERIODS.has(candidate as LeaderboardPeriod)) {
    throw new HttpError(400, `Unsupported leaderboard period: ${candidate}`, 'INVALID_LEADERBOARD_PERIOD');
  }
  return candidate as LeaderboardPeriod;
}

function normalizeUuidParam(ctx: RequestContext, name: string) {
  const value = stringParam(ctx, name);
  if (!value) return '';
  if (!/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(value)) {
    throw new HttpError(400, `${name} must be a UUID`, 'INVALID_UUID_PARAM');
  }
  return value;
}

function windowCondition(period: LeaderboardPeriod, column: string) {
  if (period === '7d') return `${column} >= now() - interval '7 days'`;
  if (period === '30d') return `${column} >= now() - interval '30 days'`;
  return 'true';
}

function metricLabel(metric: LeaderboardMetric) {
  return {
    questions: '累计答题',
    score: '积分',
    vocabulary: '掌握单词',
    mock_exam: '模考最高分',
  }[metric];
}

function metricUnit(metric: LeaderboardMetric) {
  return {
    questions: '题',
    score: '分',
    vocabulary: '词',
    mock_exam: '分',
  }[metric];
}

async function assertLeaderboardEnabled(tenantId: string) {
  const settings = await queryOne<{ enabled: boolean }>(
    `
    select coalesce(feature_flags ->> 'enableLeaderboard', 'false') = 'true' as enabled
    from public.tenant_settings
    where tenant_id = $1
    limit 1
    `,
    [tenantId],
  );
  if (!settings?.enabled) {
    throw new HttpError(403, 'Leaderboard is disabled for this tenant', 'LEADERBOARD_DISABLED');
  }
}

function rowToItem(row: RankedRow, currentUserId: string) {
  return {
    rank: Number(row.rank),
    userId: row.userId,
    displayName: row.name || row.username || '学员',
    avatarUrl: `/assets/avatars/default-${row.avatarPreset}.svg`,
    avatarPreset: row.avatarPreset,
    primaryRole: row.primaryRole,
    regionId: row.regionId,
    regionName: row.regionName,
    classIds: row.classIds || [],
    classNames: row.classNames || [],
    value: Number(row.value || 0),
    secondaryValue: row.secondaryValue === null || row.secondaryValue === undefined ? null : Number(row.secondaryValue),
    latestAt: row.latestAt,
    isCurrentUser: row.userId === currentUserId,
  };
}

function baseUsersCte(filters: string[]) {
  return `
    base_users as (
      select u.id as user_id, u.username, u.name,
             coalesce(sp.avatar_preset, 'male') as avatar_preset,
             u.primary_role,
             sp.region_id, r.name as region_name,
             coalesce(array_remove(array_agg(distinct tcm.class_id) filter (where tcm.class_id is not null), null), array[]::uuid[]) as class_ids,
             coalesce(array_remove(array_agg(distinct tc.name) filter (where tc.name is not null), null), array[]::text[]) as class_names
      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.regions r on r.id = sp.region_id and r.tenant_id = tm.tenant_id
      left join public.tenant_class_members tcm
        on tcm.tenant_id = tm.tenant_id
       and tcm.user_id = tm.user_id
       and tcm.member_type = 'student'
       and tcm.status = 'active'
      left join public.tenant_classes tc
        on tc.tenant_id = tcm.tenant_id
       and tc.id = tcm.class_id
       and tc.status = 'active'
      where ${filters.join(' and ')}
      group by u.id, u.username, u.name, sp.avatar_preset, u.primary_role, sp.region_id, r.name
    )
  `;
}

function rankedSelect(metricSql: string, limitPlaceholder: string, offsetPlaceholder: string) {
  return `
    ${metricSql},
    ranked as (
      select bu.user_id as "userId", bu.username, bu.name, bu.avatar_preset as "avatarPreset",
             bu.primary_role as "primaryRole", bu.region_id as "regionId", bu.region_name as "regionName",
             bu.class_ids as "classIds", bu.class_names as "classNames",
             coalesce(m.value, 0) as value,
             m.secondary_value as "secondaryValue",
             m.latest_at as "latestAt",
             dense_rank() over (
               order by coalesce(m.value, 0) desc,
                        coalesce(m.secondary_value, 0) desc,
                        coalesce(m.latest_at, '1970-01-01'::timestamptz) asc,
                        bu.user_id asc
             ) as rank
      from base_users bu
      left join metric_values m on m.user_id = bu.user_id
    )
    select *
    from ranked
    where value > 0
    order by rank asc, value desc, "userId" asc
    limit ${limitPlaceholder}
    offset ${offsetPlaceholder}
  `;
}

function currentRankSelect(metricSql: string, userPlaceholder: string) {
  return `
    ${metricSql},
    ranked as (
      select bu.user_id as "userId", bu.username, bu.name, bu.avatar_preset as "avatarPreset",
             bu.primary_role as "primaryRole", bu.region_id as "regionId", bu.region_name as "regionName",
             bu.class_ids as "classIds", bu.class_names as "classNames",
             coalesce(m.value, 0) as value,
             m.secondary_value as "secondaryValue",
             m.latest_at as "latestAt",
             dense_rank() over (
               order by coalesce(m.value, 0) desc,
                        coalesce(m.secondary_value, 0) desc,
                        coalesce(m.latest_at, '1970-01-01'::timestamptz) asc,
                        bu.user_id asc
             ) as rank
      from base_users bu
      left join metric_values m on m.user_id = bu.user_id
    )
    select *
    from ranked
    where "userId" = ${userPlaceholder}::uuid
    limit 1
  `;
}

function buildMetricCte(metric: LeaderboardMetric, period: LeaderboardPeriod, tenantParam = '$1') {
  if (metric === 'questions') {
    return `
      metric_values as (
        select ar.user_id,
               count(*)::numeric as value,
               count(*) filter (where ar.is_correct is true)::numeric as secondary_value,
               max(ar.answered_at) as latest_at
        from public.answer_records ar
        where ar.tenant_id = ${tenantParam}
          and ${windowCondition(period, 'ar.answered_at')}
        group by ar.user_id
      )
    `;
  }

  if (metric === 'score') {
    if (period === 'all') {
      return `
        metric_values as (
          select u.id as user_id,
                 u.score::numeric as value,
                 null::numeric as secondary_value,
                 u.updated_at as latest_at
          from public.platform_users u
        )
      `;
    }
    return `
      metric_values as (
        select e.user_id,
               coalesce(sum(e.points), 0)::numeric as value,
               count(*)::numeric as secondary_value,
               max(e.created_at) as latest_at
        from public.user_score_events e
        where e.tenant_id = ${tenantParam}
          and e.points > 0
          and ${windowCondition(period, 'e.created_at')}
        group by e.user_id
      )
    `;
  }

  if (metric === 'vocabulary') {
    if (period === 'all') {
      return `
        metric_values as (
          select sp.user_id,
                 greatest(
                   sp.mastered_words_count,
                   coalesce(count(p.word_id) filter (where p.status = 'mastered'), 0)
                 )::numeric as value,
                 coalesce(count(p.word_id) filter (where p.status in ('learning', 'reviewing')), 0)::numeric as secondary_value,
                 max(p.last_review_date) as latest_at
          from public.student_profiles sp
          left join public.user_word_progress p
            on p.tenant_id = sp.tenant_id
           and p.user_id = sp.user_id
          where sp.tenant_id = ${tenantParam}
          group by sp.user_id, sp.mastered_words_count
        )
      `;
    }
    return `
      metric_values as (
        select p.user_id,
               count(distinct p.word_id)::numeric as value,
               count(*) filter (where p.status = 'mastered')::numeric as secondary_value,
               max(p.last_review_date) as latest_at
        from public.user_word_progress p
        where p.tenant_id = ${tenantParam}
          and p.last_review_date is not null
          and ${windowCondition(period, 'p.last_review_date')}
        group by p.user_id
      )
    `;
  }

  return `
    metric_values as (
      select r.user_id,
             max(r.score)::numeric as value,
             max(r.accuracy)::numeric as secondary_value,
             max(r.submitted_at) as latest_at
      from public.practice_session_reports r
      where r.tenant_id = ${tenantParam}
        and r.mode = 'mock_exam'
        and ${windowCondition(period, 'r.submitted_at')}
      group by r.user_id
    )
  `;
}

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

  const metric = metricFrom(stringParam(ctx, 'metric'));
  const period = periodFrom(stringParam(ctx, 'period'));
  const limit = intParam(ctx, 'limit', 25, 100);
  const page = intParam(ctx, 'page', 1, 10000);
  const offset = (page - 1) * limit;
  const regionId = normalizeUuidParam(ctx, 'regionId');
  const classId = normalizeUuidParam(ctx, 'classId');

  const params: unknown[] = [tenantId];
  const filters = [
    'tm.tenant_id = $1',
    `tm.status = 'active'`,
    `tm.role = 'student'`,
    `u.primary_role <> 'platform_admin'`,
  ];
  if (regionId) {
    params.push(regionId);
    filters.push(`sp.region_id = $${params.length}::uuid`);
  }
  if (classId) {
    params.push(classId);
    filters.push(`exists (
      select 1
      from public.tenant_class_members class_filter
      where class_filter.tenant_id = tm.tenant_id
        and class_filter.class_id = $${params.length}::uuid
        and class_filter.user_id = tm.user_id
        and class_filter.member_type = 'student'
        and class_filter.status = 'active'
    )`);
  }

  const baseSql = baseUsersCte(filters);
  const metricSql = `${baseSql},${buildMetricCte(metric, period)}`;
  const listParams = [...params, limit, offset];
  const items = await query<RankedRow>(
    `
    with ${rankedSelect(metricSql, `$${params.length + 1}`, `$${params.length + 2}`)}
    `,
    listParams,
  );

  const currentUser = await queryOne<RankedRow>(
    `
    with ${currentRankSelect(metricSql, `$${params.length + 1}`)}
    `,
    [...params, userId],
  );

  return {
    metric,
    label: metricLabel(metric),
    unit: metricUnit(metric),
    period,
    scope: {
      tenantId,
      regionId: regionId || null,
      classId: classId || null,
    },
    page,
    pageSize: limit,
    items: items.map(item => rowToItem(item, userId)),
    currentUser: currentUser ? rowToItem(currentUser, userId) : null,
    generatedAt: new Date().toISOString(),
  };
}
