import { createHash, randomUUID } from 'node:crypto';
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 {
  hasTenantPermission,
  requireTenantAdmin,
  requireTenantPermission,
  type TenantAdminAuth,
} from './auth.js';
import {
  defaultSupervisionDueAt,
  ensureReadableSupervisionClass,
  generateStudentSupervisionFollowups,
  loadStudentSupervisionCandidates,
  MAX_SUPERVISION_GENERATE,
  nextSupervisionRunAt,
  parseSupervisionSchedule,
  parseSupervisionRules,
  scopedSupervisionClassIds,
  supervisionBatchKey,
} from './supervision.js';

type JsonBody = Record<string, unknown>;

const CLASS_STATUSES = ['active', 'disabled', 'archived'];
const CLASS_MEMBER_TYPES = ['student', 'teacher', 'assistant', 'head_teacher'];
const CLASS_MEMBER_STATUSES = ['active', 'disabled', 'removed'];
const STUDENT_MEMBER_STATUSES = ['active', 'invited', 'disabled'];
const STUDENT_NOTE_TYPES = ['general', 'learning', 'service', 'sales', 'risk', 'follow_up'];
const STUDENT_NOTE_VISIBILITIES = ['tenant_staff', 'class_staff', 'author_only'];
const STUDENT_FOLLOWUP_TYPES = ['learning', 'service', 'sales', 'renewal', 'risk', 'custom'];
const STUDENT_FOLLOWUP_PRIORITIES = ['low', 'normal', 'high', 'urgent'];
const STUDENT_FOLLOWUP_STATUSES = ['open', 'in_progress', 'done', 'cancelled'];
const STUDENT_SUPERVISION_RULE_STATUSES = ['active', 'disabled', 'archived'];
const FOLLOWUP_REPORT_RANGES: Record<string, number> = { '7d': 7, '30d': 30, '90d': 90 };
const MAX_BULK_STUDENTS = 200;
const MAX_BULK_CLASS_ASSIGNMENTS = 500;
const MAX_BULK_CRM_PUSH = 100;
const CRM_ASSIGNABLE_ROLES = ['tenant_owner', 'tenant_admin', 'tenant_operator', 'teacher', 'sales', 'agent'];
const STUDENT_AVATAR_FIELD_KEYS = ['avatarUrl', 'avatar_url', 'avatar', 'headimgurl', 'headImgUrl', 'figureurl', 'figureurl_qq_1', 'figureurl_qq_2'];
const STUDENT_PRIMARY_ROLE_FIELD_KEYS = ['primaryRole', 'primary_role'];
const UUID_PATTERN = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
const DATE_KEY_PATTERN = /^\d{4}-\d{2}-\d{2}$/;

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

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

function limitedNullableString(value: unknown, maxLength: number, fieldName: string) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  if (candidate.length > maxLength) {
    throw new HttpError(400, `${fieldName} is too long`, 'FIELD_TOO_LONG');
  }
  return candidate;
}

function requiredLimitedString(body: JsonBody, key: string, maxLength: number) {
  const value = requiredString(body, key);
  if (value.length > maxLength) {
    throw new HttpError(400, `${key} is too long`, 'FIELD_TOO_LONG');
  }
  return value;
}

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

function jsonBodyValue(value: unknown) {
  return JSON.stringify(objectValue(value));
}

function hasOwnField(value: Record<string, unknown>, fieldNames: string[]) {
  return fieldNames.some(fieldName => Object.hasOwn(value, fieldName));
}

function rejectStudentAvatarFields(body: JsonBody) {
  if (!hasOwnField(body, STUDENT_AVATAR_FIELD_KEYS)) return;
  throw new HttpError(
    400,
    'Student avatars are preset-only. Do not submit avatar URLs or provider avatar fields.',
    'STUDENT_AVATAR_URL_UNSUPPORTED',
  );
}

function rejectStudentPrimaryRoleFields(body: JsonBody) {
  if (!hasOwnField(body, STUDENT_PRIMARY_ROLE_FIELD_KEYS)) return;
  throw new HttpError(
    400,
    'Student management cannot update platform primary roles. Use tenant memberships and role templates.',
    'STUDENT_PRIMARY_ROLE_UNSUPPORTED',
  );
}

function intValue(value: unknown, fallback: number) {
  const numberValue = Number(value ?? fallback);
  return Number.isFinite(numberValue) ? Math.trunc(numberValue) : fallback;
}

function boundedIntValue(value: unknown, fallback: number, min: number, max: number) {
  const parsed = intValue(value, fallback);
  return Math.max(min, Math.min(max, parsed));
}

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

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

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

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 uuidArrayValue(value: unknown) {
  if (!Array.isArray(value)) return [];
  return value
    .map(item => (typeof item === 'string' ? item.trim() : ''))
    .filter(item => UUID_PATTERN.test(item));
}

function strictUuidArrayValue(value: unknown, fieldName: string, maxLength: number) {
  if (!Array.isArray(value)) {
    throw new HttpError(400, `${fieldName} must be an array`, 'INVALID_ARRAY_FIELD');
  }
  if (value.length > maxLength) {
    throw new HttpError(413, `${fieldName} supports at most ${maxLength} items`, 'BULK_LIMIT_EXCEEDED');
  }

  const items: string[] = [];
  const seen = new Set<string>();
  for (let index = 0; index < value.length; index += 1) {
    const item = typeof value[index] === 'string' ? value[index].trim() : '';
    if (!UUID_PATTERN.test(item)) {
      throw new HttpError(400, `${fieldName}[${index}] must be a uuid`, 'INVALID_UUID_FIELD');
    }
    const normalized = item.toLowerCase();
    if (!seen.has(normalized)) {
      seen.add(normalized);
      items.push(normalized);
    }
  }
  if (!items.length) throw new HttpError(400, `${fieldName} is required`, 'REQUIRED_FIELD');
  return items;
}

function optionalTimestampString(value: unknown, fieldName: string) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  const time = Date.parse(candidate);
  if (!Number.isFinite(time)) {
    throw new HttpError(400, `${fieldName} must be a valid datetime`, 'INVALID_DATETIME');
  }
  return new Date(time).toISOString();
}

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 isValidShanghaiDateKey(dateKey: string) {
  if (!DATE_KEY_PATTERN.test(dateKey)) return false;
  const date = new Date(`${dateKey}T00:00:00+08:00`);
  return Number.isFinite(date.getTime()) && shanghaiDateKey(date) === dateKey;
}

function daysBetweenInclusive(startDate: string, endDate: string) {
  const start = Date.parse(`${startDate}T00:00:00+08:00`);
  const end = Date.parse(`${endDate}T00:00:00+08:00`);
  if (!Number.isFinite(start) || !Number.isFinite(end) || end < start) return 0;
  return Math.floor((end - start) / 86_400_000) + 1;
}

function parseFollowupReportRange(timeRangeValue: string, startDateValue: string, endDateValue: string) {
  if (startDateValue || endDateValue) {
    if (!isValidShanghaiDateKey(startDateValue) || !isValidShanghaiDateKey(endDateValue)) {
      throw new HttpError(400, 'startDate and endDate must use YYYY-MM-DD', 'INVALID_DATE_RANGE');
    }
    const days = daysBetweenInclusive(startDateValue, endDateValue);
    if (days <= 0) throw new HttpError(400, 'endDate must be greater than or equal to startDate', 'INVALID_DATE_RANGE');
    if (days > 180) throw new HttpError(400, 'Follow-up report range supports at most 180 days', 'DATE_RANGE_TOO_LARGE');
    return {
      timeRange: 'custom',
      days,
      startDate: startDateValue,
      endDate: endDateValue,
      startAt: shanghaiDayStartIso(startDateValue),
      endAt: shanghaiDayStartIso(addDaysKey(endDateValue, 1)),
    };
  }

  const timeRange = timeRangeValue || '30d';
  const days = FOLLOWUP_REPORT_RANGES[timeRange];
  if (!days) throw new HttpError(400, 'Unsupported follow-up report time range', 'INVALID_FOLLOWUP_REPORT_RANGE');
  const endDate = shanghaiDateKey(new Date());
  const startDate = addDaysKey(endDate, -(days - 1));
  return {
    timeRange,
    days,
    startDate,
    endDate,
    startAt: shanghaiDayStartIso(startDate),
    endAt: shanghaiDayStartIso(addDaysKey(endDate, 1)),
  };
}

function classCodeValue(value: unknown) {
  const code = nullableString(value);
  if (!code) return null;
  if (!/^[a-z0-9][a-z0-9_-]{1,63}$/i.test(code)) {
    throw new HttpError(400, 'Invalid class code', 'INVALID_CLASS_CODE');
  }
  return code;
}

function asObjectArray(value: unknown, fieldName: string, maxLength: number) {
  if (!Array.isArray(value)) {
    throw new HttpError(400, `${fieldName} must be an array`, 'INVALID_ARRAY_FIELD');
  }
  if (value.length > maxLength) {
    throw new HttpError(413, `${fieldName} supports at most ${maxLength} items`, 'BULK_LIMIT_EXCEEDED');
  }
  return value.map((item, index) => {
    if (!item || typeof item !== 'object' || Array.isArray(item)) {
      throw new HttpError(400, `${fieldName}[${index}] must be an object`, 'INVALID_ARRAY_ITEM');
    }
    return item as JsonBody;
  });
}

function canReadAllClassScope(auth: TenantAdminAuth) {
  return (
    auth.role === 'tenant_owner' ||
    auth.role === 'tenant_admin' ||
    hasTenantPermission(auth, 'classes:write') ||
    hasTenantPermission(auth, 'students:write') ||
    hasTenantPermission(auth, 'members:read')
  );
}

function canSeeStudentPhone(auth: TenantAdminAuth) {
  return auth.fieldPermissions?.['student.phone'] !== false;
}

function maskStudentFields<T extends Record<string, unknown>>(auth: TenantAdminAuth, item: T): T {
  if (canSeeStudentPhone(auth)) return item;
  return {
    ...item,
    phone: null,
    studentPhone: null,
  };
}

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 scopedClassIds(auth: TenantAdminAuth) {
  if (canReadAllClassScope(auth)) return null;

  const explicit = uuidArrayValue(auth.dataScope?.classIds);
  const rows = await query<{ id: string }>(
    `
    select distinct class_id as id
    from public.tenant_class_members
    where tenant_id = $1
      and user_id = $2
      and status = 'active'
      and member_type in ('teacher', 'assistant', 'head_teacher')
    `,
    [auth.tenantId, auth.userId],
  );

  return Array.from(new Set([...explicit, ...rows.map(item => item.id)]));
}

async function ensureReadableClass(auth: TenantAdminAuth, classId: string) {
  const allowedClassIds = await scopedClassIds(auth);
  if (allowedClassIds && !allowedClassIds.includes(classId)) {
    throw new HttpError(403, 'Class is outside the current data scope', 'CLASS_SCOPE_REQUIRED');
  }

  const rows = await query<{ id: string }>(
    'select id from public.tenant_classes where tenant_id = $1 and id = $2 limit 1',
    [auth.tenantId, classId],
  );
  if (!rows[0]) throw new HttpError(404, 'Class not found', 'CLASS_NOT_FOUND');
}

async function ensureStudentInScope(auth: TenantAdminAuth, studentUserId: string) {
  const scopedIds = await scopedClassIds(auth);
  const params: unknown[] = [auth.tenantId, studentUserId];
  const filters = [
    'tm.tenant_id = $1',
    'tm.user_id = $2::uuid',
    `tm.role = 'student'`,
  ];
  if (scopedIds) {
    params.push(scopedIds);
    filters.push(`exists (
      select 1 from public.tenant_class_members scoped_cm
      where scoped_cm.tenant_id = tm.tenant_id
        and scoped_cm.user_id = tm.user_id
        and scoped_cm.class_id = any($${params.length}::uuid[])
        and scoped_cm.member_type = 'student'
        and scoped_cm.status = 'active'
    )`);
  }
  const rows = await query<{ userId: string }>(
    `
    select tm.user_id as "userId"
    from public.tenant_memberships tm
    where ${filters.join(' and ')}
    limit 1
    `,
    params,
  );
  if (!rows[0]) {
    throw new HttpError(scopedIds ? 403 : 404, 'Student is outside the current data scope', 'STUDENT_SCOPE_REQUIRED');
  }
}

async function ensureUserTenantMembership(
  client: pg.PoolClient,
  tenantId: string,
  userId: string | null,
  allowedRoles: string[],
  errorCode: string,
) {
  if (!userId) return;
  const result = await client.query<{ id: string }>(
    `
    select id
    from public.tenant_memberships
    where tenant_id = $1
      and user_id = $2
      and role = any($3::text[])
      and status = 'active'
    limit 1
    `,
    [tenantId, userId, allowedRoles],
  );
  if (!result.rows[0]) throw new HttpError(400, 'User is not an active tenant member', errorCode);
}

async function ensureTenantReference(
  client: pg.PoolClient,
  tableName: 'regions' | 'schools' | 'majors',
  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);
}

async function ensureTenantClass(client: pg.PoolClient, tenantId: string, classId: string) {
  const result = await client.query<{ id: string }>(
    'select id from public.tenant_classes where tenant_id = $1 and id = $2 limit 1',
    [tenantId, classId],
  );
  if (!result.rows[0]) throw new HttpError(404, 'Class not found', 'CLASS_NOT_FOUND');
}

async function resolveOrCreateUser(client: pg.PoolClient, body: JsonBody, fallbackPrimaryRole: string) {
  if (fallbackPrimaryRole === 'student') {
    rejectStudentAvatarFields(body);
    rejectStudentPrimaryRoleFields(body);
  }
  const avatarUrl = fallbackPrimaryRole === 'student' ? null : nullableString(body.avatarUrl);
  const userId = nullableString(body.userId);
  if (userId) {
    const existing = await client.query<{ id: string }>(
      'select id from public.platform_users where id = $1 limit 1',
      [userId],
    );
    if (!existing.rows[0]) throw new HttpError(404, 'User not found', 'USER_NOT_FOUND');
    await client.query(
      `
      update public.platform_users
      set username = coalesce($2, username),
          email = coalesce($3::citext, email),
          phone = coalesce($4, phone),
          name = coalesce($5, name),
          avatar_url = coalesce($6, avatar_url),
          updated_at = now()
      where id = $1
      `,
      [
        userId,
        nullableString(body.username),
        nullableString(body.email),
        nullableString(body.phone),
        nullableString(body.name),
        avatarUrl,
      ],
    );
    return userId;
  }

  const phone = nullableString(body.phone);
  const email = nullableString(body.email);
  const username = nullableString(body.username);
  const name = nullableString(body.name);
  if (!phone && !email && !username && !name) {
    throw new HttpError(400, 'userId, phone, email, username, or name is required', 'USER_REQUIRED');
  }

  const found = await client.query<{ id: string }>(
    `
    select id
    from public.platform_users
    where ($1::text is not null and phone = $1)
       or ($2::citext is not null and email = $2::citext)
       or ($3::text is not null and username = $3)
    order by created_at asc
    limit 1
    `,
    [phone, email, username],
  );
  if (found.rows[0]) return found.rows[0].id;

  const created = await client.query<{ id: string }>(
    `
    insert into public.platform_users (username, email, phone, name, avatar_url, primary_role, raw_profile)
    values ($1, $2::citext, $3, $4, $5, $6, '{"source":"tenant-admin"}'::jsonb)
    returning id
    `,
    [
      username || phone || email,
      email,
      phone,
      name || username || phone || email,
      avatarUrl,
      fallbackPrimaryRole,
    ],
  );
  return created.rows[0].id;
}

async function ensureTenantMembership(
  client: pg.PoolClient,
  tenantId: string,
  userId: string,
  role: string,
  status = 'active',
) {
  await client.query(
    `
    insert into public.tenant_memberships (tenant_id, user_id, role, status)
    values ($1, $2, $3, $4)
    on conflict (tenant_id, user_id, role)
    do update set status = excluded.status,
                  updated_at = now()
    `,
    [tenantId, userId, role, status],
  );
}

interface StudentCrmPushConfig {
  enabled: boolean;
  url: string | null;
  formName: string | null;
  examType: string | null;
  delaySec: number | null;
}

async function loadCrmPushConfig(client: pg.PoolClient, tenantId: string) {
  const result = await client.query<StudentCrmPushConfig>(
    `
    select enabled, url, form_name as "formName", exam_type as "examType", delay_sec as "delaySec"
    from public.crm_config
    where tenant_id = $1
    limit 1
    `,
    [tenantId],
  );
  const config = result.rows[0] || null;
  if (!config?.enabled || !config.url) {
    throw new HttpError(409, 'CRM config is disabled or missing webhook URL', 'CRM_CONFIG_REQUIRED');
  }
  return config;
}

function crmPushIdempotencyKey(inputKey: string | null, requestId: string, studentUserId: string) {
  const rawKey = inputKey || requestId;
  const normalized = rawKey.length <= 80 ? rawKey : createHash('sha256').update(rawKey).digest('hex');
  return `student_crm_push:${normalized}:${studentUserId}`;
}

async function loadStudentCrmPayload(
  client: pg.PoolClient,
  auth: TenantAdminAuth,
  studentUserId: string,
  includePhone: boolean,
) {
  const result = await client.query<Record<string, unknown>>(
    `
    with class_agg as (
      select tcm.tenant_id, tcm.user_id,
             jsonb_agg(
               jsonb_build_object(
                 'classId', tc.id,
                 'className', tc.name,
                 'classCode', tc.code
               )
               order by tc.sort_order asc, tc.created_at desc
             ) filter (where tcm.status = 'active') as classes
      from public.tenant_class_members tcm
      join public.tenant_classes tc on tc.tenant_id = tcm.tenant_id and tc.id = tcm.class_id
      where tcm.tenant_id = $1
        and tcm.user_id = $2::uuid
        and tcm.member_type = 'student'
      group by tcm.tenant_id, tcm.user_id
    )
    select u.id as "userId", u.username, u.name,
           case when $3::boolean then u.phone else null end as phone,
           u.email::text as email,
           coalesce(sp.avatar_preset, 'male') as "avatarPreset",
           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",
           coalesce(ca.classes, '[]'::jsonb) as classes
    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.tenant_id = tm.tenant_id and r.id = sp.region_id
    left join public.schools s on s.tenant_id = tm.tenant_id and s.id = sp.selected_school_id
    left join public.majors m on m.tenant_id = tm.tenant_id and m.id = sp.selected_major_id
    left join class_agg ca on ca.tenant_id = tm.tenant_id and ca.user_id = tm.user_id
    where tm.tenant_id = $1
      and tm.user_id = $2::uuid
      and tm.role = 'student'
      and tm.status = 'active'
    limit 1
    `,
    [auth.tenantId, studentUserId, includePhone],
  );
  if (!result.rows[0]) throw new HttpError(404, 'Student not found', 'STUDENT_NOT_FOUND');
  return result.rows[0];
}

async function loadCrmAssigneePayload(client: pg.PoolClient, tenantId: string, userId: string | null) {
  if (!userId) return null;
  const result = await client.query<Record<string, unknown>>(
    `
    select u.id as "userId", u.username, u.name, tm.role
    from public.tenant_memberships tm
    join public.platform_users u on u.id = tm.user_id
    where tm.tenant_id = $1
      and tm.user_id = $2::uuid
      and tm.role = any($3::text[])
      and tm.status = 'active'
    order by case tm.role
      when 'tenant_owner' then 1
      when 'tenant_admin' then 2
      when 'tenant_operator' then 3
      when 'teacher' then 4
      when 'sales' then 5
      when 'agent' then 6
      else 9
    end
    limit 1
    `,
    [tenantId, userId, CRM_ASSIGNABLE_ROLES],
  );
  return result.rows[0] || null;
}

async function loadCrmClassPayload(client: pg.PoolClient, tenantId: string, classId: string | null) {
  if (!classId) return null;
  const result = await client.query<Record<string, unknown>>(
    `
    select id as "classId", code as "classCode", name as "className"
    from public.tenant_classes
    where tenant_id = $1 and id = $2::uuid
    limit 1
    `,
    [tenantId, classId],
  );
  return result.rows[0] || null;
}

async function ensureStudentInClass(client: pg.PoolClient, tenantId: string, studentUserId: string, classId: string | null) {
  if (!classId) return;
  const result = await client.query<{ id: string }>(
    `
    select id
    from public.tenant_class_members
    where tenant_id = $1
      and class_id = $2::uuid
      and user_id = $3::uuid
      and member_type = 'student'
      and status = 'active'
    limit 1
    `,
    [tenantId, classId, studentUserId],
  );
  if (!result.rows[0]) {
    throw new HttpError(400, 'Student is not an active member of the selected class', 'STUDENT_CLASS_REQUIRED');
  }
}

function membershipRoleForClassMember(memberType: string) {
  return memberType === 'student' ? 'student' : 'teacher';
}

export async function tenantClassesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const regionId = stringParam(ctx, 'regionId');
  const keyword = stringParam(ctx, 'keyword');
  const scopedIds = await scopedClassIds(auth);

  const params: unknown[] = [auth.tenantId];
  const filters = ['tc.tenant_id = $1'];
  if (status) {
    if (!CLASS_STATUSES.includes(status)) throw new HttpError(400, `Invalid class status: ${status}`, 'INVALID_CLASS_STATUS');
    params.push(status);
    filters.push(`tc.status = $${params.length}`);
  } else {
    filters.push(`tc.status <> 'archived'`);
  }
  if (regionId) {
    params.push(regionId);
    filters.push(`tc.region_id = $${params.length}::uuid`);
  }
  if (keyword) {
    params.push(`%${keyword}%`);
    filters.push(`(tc.name ilike $${params.length} or tc.code ilike $${params.length})`);
  }
  if (scopedIds) {
    params.push(scopedIds);
    filters.push(`tc.id = any($${params.length}::uuid[])`);
  }
  params.push(limit);

  const items = await query(
    `
    select tc.id, tc.region_id as "regionId", r.name as "regionName",
           tc.legacy_id as "legacyId", tc.code, tc.name, tc.description,
           tc.status, tc.sort_order as "sortOrder", tc.metadata,
           tc.created_by as "createdBy", tc.updated_by as "updatedBy",
           tc.created_at as "createdAt", tc.updated_at as "updatedAt",
           coalesce(count(tcm.id) filter (where tcm.status = 'active' and tcm.member_type = 'student'), 0)::int as "studentCount",
           coalesce(count(tcm.id) filter (where tcm.status = 'active' and tcm.member_type in ('teacher', 'assistant', 'head_teacher')), 0)::int as "teacherCount"
    from public.tenant_classes tc
    left join public.regions r on r.id = tc.region_id and r.tenant_id = tc.tenant_id
    left join public.tenant_class_members tcm on tcm.tenant_id = tc.tenant_id and tcm.class_id = tc.id
    where ${filters.join(' and ')}
    group by tc.id, r.name
    order by tc.sort_order asc, tc.created_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items, scoped: scopedIds !== null };
}

export async function upsertTenantClassRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:write');
  const body = await readJsonBody(ctx);
  const item = await transaction(async client => {
    const regionId = nullableString(body.regionId);
    await ensureTenantReference(client, 'regions', auth.tenantId, regionId, 'REGION_NOT_FOUND');

    const result = await client.query(
      `
      insert into public.tenant_classes (
        id, tenant_id, region_id, legacy_id, code, name, description,
        status, sort_order, metadata, created_by, updated_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3::uuid, $4, $5, $6, $7,
        $8, $9, $10::jsonb, $11, $11
      )
      on conflict (id)
      do update set region_id = excluded.region_id,
                    legacy_id = coalesce(excluded.legacy_id, public.tenant_classes.legacy_id),
                    code = excluded.code,
                    name = excluded.name,
                    description = excluded.description,
                    status = excluded.status,
                    sort_order = excluded.sort_order,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.tenant_classes.tenant_id = excluded.tenant_id
      returning id, region_id as "regionId", legacy_id as "legacyId", code, name,
                description, status, sort_order as "sortOrder", metadata,
                created_by as "createdBy", updated_by as "updatedBy",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        regionId,
        nullableString(body.legacyId),
        classCodeValue(body.code),
        requiredString(body, 'name'),
        nullableString(body.description),
        optionalChoice(body.status, CLASS_STATUSES, 'active'),
        intValue(body.sortOrder, 0),
        jsonBodyValue(body.metadata),
        auth.userId,
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Class not found for this tenant', 'CLASS_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.class.upserted', 'tenant_classes', result.rows[0].id, {
      code: result.rows[0].code,
      name: result.rows[0].name,
    });
    return result.rows[0];
  });
  return { item };
}

export async function disableTenantClassRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:write');
  const body = await readJsonBody(ctx);
  const classId = requiredString(body, 'classId');
  const item = await transaction(async client => {
    const result = await client.query(
      `
      update public.tenant_classes
      set status = 'disabled',
          updated_by = $3,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, code, name, status, updated_at as "updatedAt"
      `,
      [auth.tenantId, classId, auth.userId],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Class not found', 'CLASS_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.class.disabled', 'tenant_classes', classId);
    return result.rows[0];
  });
  return { item };
}

export async function tenantClassMembersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:read');
  const classId = stringParam(ctx, 'classId');
  if (!classId) throw new HttpError(400, 'classId is required', 'CLASS_ID_REQUIRED');
  await ensureReadableClass(auth, classId);
  const memberType = stringParam(ctx, 'memberType');
  const status = stringParam(ctx, 'status') || 'active';
  if (memberType && !CLASS_MEMBER_TYPES.includes(memberType)) {
    throw new HttpError(400, `Invalid member type: ${memberType}`, 'INVALID_CLASS_MEMBER_TYPE');
  }
  if (!CLASS_MEMBER_STATUSES.includes(status)) {
    throw new HttpError(400, `Invalid class member status: ${status}`, 'INVALID_CLASS_MEMBER_STATUS');
  }
  const params: unknown[] = [auth.tenantId, classId, status];
  const filters = ['tcm.tenant_id = $1', 'tcm.class_id = $2::uuid', 'tcm.status = $3'];
  if (memberType) {
    params.push(memberType);
    filters.push(`tcm.member_type = $${params.length}`);
  }

  const items = await query<Record<string, unknown>>(
    `
    select tcm.id, tcm.class_id as "classId", tcm.user_id as "userId",
           tcm.member_type as "memberType", tcm.status, tcm.joined_at as "joinedAt",
           tcm.left_at as "leftAt", tcm.metadata, tcm.created_at as "createdAt",
           tcm.updated_at as "updatedAt",
           u.username, u.email::text as email, u.phone, u.name,
           case when tcm.member_type = 'student' then null else u.avatar_url end as "avatarUrl",
           u.primary_role as "primaryRole",
           sp.region_id as "regionId", sp.selected_school_id as "selectedSchoolId",
           sp.selected_major_id as "selectedMajorId"
    from public.tenant_class_members tcm
    join public.platform_users u on u.id = tcm.user_id
    left join public.student_profiles sp on sp.tenant_id = tcm.tenant_id and sp.user_id = tcm.user_id
    where ${filters.join(' and ')}
    order by case tcm.member_type
      when 'head_teacher' then 1
      when 'teacher' then 2
      when 'assistant' then 3
      else 9
    end, tcm.joined_at asc
    `,
    params,
  );

  return { items: items.map(item => maskStudentFields(auth, item)) };
}

export async function upsertTenantClassMemberRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:write');
  const body = await readJsonBody(ctx);
  const classId = requiredString(body, 'classId');
  const memberType = optionalChoice(body.memberType, CLASS_MEMBER_TYPES, 'student');
  const status = optionalChoice(body.status, CLASS_MEMBER_STATUSES, 'active');

  const item = await transaction(async client => {
    await ensureTenantClass(client, auth.tenantId, classId);
    const userId = await resolveOrCreateUser(client, body, membershipRoleForClassMember(memberType));
    await ensureTenantMembership(client, auth.tenantId, userId, membershipRoleForClassMember(memberType), 'active');
    if (memberType === 'student') {
      await client.query(
        `
        insert into public.student_profiles (tenant_id, user_id, stats, progress)
        values ($1, $2, '{}'::jsonb, '{}'::jsonb)
        on conflict (tenant_id, user_id) do nothing
        `,
        [auth.tenantId, userId],
      );
    }

    const result = await client.query(
      `
      insert into public.tenant_class_members (
        tenant_id, class_id, user_id, member_type, status, left_at,
        metadata, created_by, updated_by
      )
      values ($1, $2, $3, $4, $5, case when $5 in ('disabled', 'removed') then now() else null end, $6::jsonb, $7, $7)
      on conflict (tenant_id, class_id, user_id, member_type)
      do update set status = excluded.status,
                    left_at = excluded.left_at,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      returning id, class_id as "classId", user_id as "userId",
                member_type as "memberType", status, joined_at as "joinedAt",
                left_at as "leftAt", metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [auth.tenantId, classId, userId, memberType, status, jsonBodyValue(body.metadata), auth.userId],
    );

    await recordAudit(client, auth, 'tenant.class_member.upserted', 'tenant_class_members', result.rows[0].id, {
      classId,
      userId,
      memberType,
      status,
    });
    return result.rows[0];
  });

  return { item };
}

export async function removeTenantClassMemberRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:write');
  const body = await readJsonBody(ctx);
  const classMemberId = requiredString(body, 'classMemberId');

  const item = await transaction(async client => {
    const result = await client.query(
      `
      update public.tenant_class_members
      set status = 'removed',
          left_at = now(),
          updated_by = $3,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, class_id as "classId", user_id as "userId",
                member_type as "memberType", status, left_at as "leftAt",
                updated_at as "updatedAt"
      `,
      [auth.tenantId, classMemberId, auth.userId],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Class member not found', 'CLASS_MEMBER_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.class_member.removed', 'tenant_class_members', classMemberId, {
      classId: result.rows[0].classId,
      userId: result.rows[0].userId,
    });
    return result.rows[0];
  });

  return { item };
}

export async function tenantStudentsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const keyword = stringParam(ctx, 'keyword');
  const status = stringParam(ctx, 'status') || 'active';
  const regionId = stringParam(ctx, 'regionId');
  const classId = stringParam(ctx, 'classId');
  const scopedIds = await scopedClassIds(auth);
  if (classId) await ensureReadableClass(auth, classId);
  if (!STUDENT_MEMBER_STATUSES.includes(status)) {
    throw new HttpError(400, `Invalid student status: ${status}`, 'INVALID_STUDENT_STATUS');
  }

  const params: unknown[] = [auth.tenantId, status];
  let classAggScopeSql = '';
  if (scopedIds) {
    params.push(scopedIds);
    classAggScopeSql = `and tcm.class_id = any($${params.length}::uuid[])`;
  }
  const filters = ['tm.tenant_id = $1', `tm.role = 'student'`, 'tm.status = $2'];
  if (keyword) {
    params.push(`%${keyword}%`);
    filters.push(`(u.username ilike $${params.length} or u.name ilike $${params.length} or u.phone ilike $${params.length} or u.email::text ilike $${params.length})`);
  }
  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 scoped_cm
      where scoped_cm.tenant_id = tm.tenant_id
        and scoped_cm.user_id = tm.user_id
        and scoped_cm.class_id = $${params.length}::uuid
        and scoped_cm.member_type = 'student'
        and scoped_cm.status = 'active'
    )`);
  }
  if (scopedIds) {
    params.push(scopedIds);
    filters.push(`exists (
      select 1 from public.tenant_class_members scoped_cm
      where scoped_cm.tenant_id = tm.tenant_id
        and scoped_cm.user_id = tm.user_id
        and scoped_cm.class_id = any($${params.length}::uuid[])
        and scoped_cm.member_type = 'student'
        and scoped_cm.status = 'active'
    )`);
  }
  params.push(limit);

  const items = await query<Record<string, unknown>>(
    `
    with class_agg as (
      select tcm.tenant_id, tcm.user_id,
             jsonb_agg(
               jsonb_build_object(
                 'classId', tc.id,
                 'className', tc.name,
                 'classCode', tc.code,
                 'memberType', tcm.member_type,
                 'joinedAt', tcm.joined_at
               )
               order by tc.sort_order asc, tc.created_at desc
             ) filter (where tcm.status = 'active') as classes
      from public.tenant_class_members tcm
      join public.tenant_classes tc on tc.tenant_id = tcm.tenant_id and tc.id = tcm.class_id
      where tcm.tenant_id = $1 and tcm.member_type = 'student'
        ${classAggScopeSql}
      group by tcm.tenant_id, tcm.user_id
    )
    select tm.id as "membershipId", tm.user_id as "userId", tm.status,
           tm.created_at as "memberCreatedAt", tm.updated_at as "memberUpdatedAt",
           u.username, u.email::text as email, u.phone, u.name,
           null::text as "avatarUrl", u.primary_role as "primaryRole",
           u.last_seen_at as "lastSeenAt",
           sp.id as "profileId", 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",
           coalesce(ca.classes, '[]'::jsonb) as classes
    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.tenant_id = tm.tenant_id and r.id = sp.region_id
    left join public.schools s on s.tenant_id = tm.tenant_id and s.id = sp.selected_school_id
    left join public.majors m on m.tenant_id = tm.tenant_id and m.id = sp.selected_major_id
    left join class_agg ca on ca.tenant_id = tm.tenant_id and ca.user_id = tm.user_id
    where ${filters.join(' and ')}
    order by tm.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items: items.map(item => maskStudentFields(auth, item)), scoped: scopedIds !== null };
}

export async function upsertTenantStudentRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:write');
  const body = await readJsonBody(ctx);
  const status = optionalChoice(body.status, STUDENT_MEMBER_STATUSES, 'active');

  const item = await transaction(async client => {
    const userId = await resolveOrCreateUser(client, body, 'student');
    const regionId = nullableString(body.regionId);
    const selectedSchoolId = nullableString(body.selectedSchoolId);
    const selectedMajorId = nullableString(body.selectedMajorId);
    await ensureTenantReference(client, 'regions', auth.tenantId, regionId, 'REGION_NOT_FOUND');
    await ensureTenantReference(client, 'schools', auth.tenantId, selectedSchoolId, 'SCHOOL_NOT_FOUND');
    await ensureTenantReference(client, 'majors', auth.tenantId, selectedMajorId, 'MAJOR_NOT_FOUND');

    await ensureTenantMembership(client, auth.tenantId, userId, 'student', status);
    const profile = await client.query(
      `
      insert into public.student_profiles (
        tenant_id, user_id, region_id, selected_school_id, selected_major_id,
        stats, progress, module_selections
      )
      values ($1, $2, $3::uuid, $4::uuid, $5::uuid, $6::jsonb, $7::jsonb, $8::jsonb)
      on conflict (tenant_id, user_id)
      do update set 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 $6::jsonb = '{}'::jsonb then public.student_profiles.stats else excluded.stats end,
                    progress = case when $7::jsonb = '{}'::jsonb then public.student_profiles.progress else excluded.progress end,
                    module_selections = case when $8::jsonb = '{}'::jsonb then public.student_profiles.module_selections else excluded.module_selections end,
                    updated_at = now()
      returning id as "profileId", tenant_id as "tenantId", user_id as "userId",
                region_id as "regionId", selected_school_id as "selectedSchoolId",
                selected_major_id as "selectedMajorId", stats, progress,
                module_selections as "moduleSelections", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        userId,
        regionId,
        selectedSchoolId,
        selectedMajorId,
        jsonBodyValue(body.stats),
        jsonBodyValue(body.progress),
        jsonBodyValue(body.moduleSelections),
      ],
    );

    await recordAudit(client, auth, 'tenant.student.upserted', 'student_profiles', profile.rows[0].profileId, {
      userId,
      status,
      regionId,
      selectedSchoolId,
      selectedMajorId,
    });
    return profile.rows[0];
  });

  return { item };
}

export async function updateTenantStudentStatusRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:status:write');
  const body = await readJsonBody(ctx);
  const userId = requiredString(body, 'userId');
  const status = optionalChoice(body.status, STUDENT_MEMBER_STATUSES, 'active');

  const item = await transaction(async client => {
    const result = await client.query(
      `
      update public.tenant_memberships
      set status = $3,
          updated_at = now()
      where tenant_id = $1
        and user_id = $2
        and role = 'student'
      returning id as "membershipId", user_id as "userId", role, status, updated_at as "updatedAt"
      `,
      [auth.tenantId, userId, status],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Student membership not found', 'STUDENT_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.student.status_updated', 'tenant_memberships', result.rows[0].membershipId, {
      userId,
      status,
      reason: nullableString(body.reason),
    });
    return result.rows[0];
  });

  return { item };
}

export async function bulkUpsertTenantStudentsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:bulk:write');
  const body = await readJsonBody(ctx);
  const students = asObjectArray(body.students, 'students', MAX_BULK_STUDENTS);

  const result = await transaction(async client => {
    const items: unknown[] = [];
    const errors: unknown[] = [];

    for (let index = 0; index < students.length; index += 1) {
      const student = students[index];
      try {
        const userId = await resolveOrCreateUser(client, student, 'student');
        const status = optionalChoice(student.status, STUDENT_MEMBER_STATUSES, 'active');
        const regionId = nullableString(student.regionId);
        const selectedSchoolId = nullableString(student.selectedSchoolId);
        const selectedMajorId = nullableString(student.selectedMajorId);
        await ensureTenantReference(client, 'regions', auth.tenantId, regionId, 'REGION_NOT_FOUND');
        await ensureTenantReference(client, 'schools', auth.tenantId, selectedSchoolId, 'SCHOOL_NOT_FOUND');
        await ensureTenantReference(client, 'majors', auth.tenantId, selectedMajorId, 'MAJOR_NOT_FOUND');
        await ensureTenantMembership(client, auth.tenantId, userId, 'student', status);

        const profile = await client.query(
          `
          insert into public.student_profiles (
            tenant_id, user_id, legacy_user_id, region_id, selected_school_id,
            selected_major_id, stats, progress, module_selections
          )
          values ($1, $2, $3, $4::uuid, $5::uuid, $6::uuid, $7::jsonb, $8::jsonb, $9::jsonb)
          on conflict (tenant_id, user_id)
          do update set legacy_user_id = coalesce(excluded.legacy_user_id, public.student_profiles.legacy_user_id),
                        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 $7::jsonb = '{}'::jsonb then public.student_profiles.stats else excluded.stats end,
                        progress = case when $8::jsonb = '{}'::jsonb then public.student_profiles.progress else excluded.progress end,
                        module_selections = case when $9::jsonb = '{}'::jsonb then public.student_profiles.module_selections else excluded.module_selections end,
                        updated_at = now()
          returning id as "profileId", user_id as "userId", region_id as "regionId",
                    selected_school_id as "selectedSchoolId", selected_major_id as "selectedMajorId"
          `,
          [
            auth.tenantId,
            userId,
            nullableString(student.legacyUserId),
            regionId,
            selectedSchoolId,
            selectedMajorId,
            jsonBodyValue(student.stats),
            jsonBodyValue(student.progress),
            jsonBodyValue(student.moduleSelections),
          ],
        );
        items.push({ index, ...profile.rows[0], status });
      } catch (error) {
        errors.push({
          index,
          code: error instanceof HttpError ? error.code : 'STUDENT_BULK_ITEM_FAILED',
          message: error instanceof Error ? error.message : 'Student import item failed',
        });
      }
    }

    await recordAudit(client, auth, 'tenant.students.bulk_upserted', 'student_profiles', null, {
      total: students.length,
      successCount: items.length,
      errorCount: errors.length,
    });
    return { total: students.length, successCount: items.length, errorCount: errors.length, items, errors };
  });

  return result;
}

export async function bulkAssignTenantClassMembersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:bulk:write');
  const body = await readJsonBody(ctx);
  const classId = requiredString(body, 'classId');
  const assignments = asObjectArray(body.assignments || body.students, 'assignments', MAX_BULK_CLASS_ASSIGNMENTS);

  const result = await transaction(async client => {
    await ensureTenantClass(client, auth.tenantId, classId);
    const items: unknown[] = [];
    const errors: unknown[] = [];

    for (let index = 0; index < assignments.length; index += 1) {
      const assignment = assignments[index];
      try {
        const memberType = optionalChoice(assignment.memberType, CLASS_MEMBER_TYPES, 'student');
        const status = optionalChoice(assignment.status, CLASS_MEMBER_STATUSES, 'active');
        const userId = await resolveOrCreateUser(client, assignment, membershipRoleForClassMember(memberType));
        await ensureTenantMembership(client, auth.tenantId, userId, membershipRoleForClassMember(memberType), 'active');
        if (memberType === 'student') {
          await client.query(
            `
            insert into public.student_profiles (tenant_id, user_id, stats, progress)
            values ($1, $2, '{}'::jsonb, '{}'::jsonb)
            on conflict (tenant_id, user_id) do nothing
            `,
            [auth.tenantId, userId],
          );
        }
        const item = await client.query(
          `
          insert into public.tenant_class_members (
            tenant_id, class_id, user_id, member_type, status, left_at,
            metadata, created_by, updated_by
          )
          values ($1, $2, $3, $4, $5, case when $5 in ('disabled', 'removed') then now() else null end, $6::jsonb, $7, $7)
          on conflict (tenant_id, class_id, user_id, member_type)
          do update set status = excluded.status,
                        left_at = excluded.left_at,
                        metadata = excluded.metadata,
                        updated_by = excluded.updated_by,
                        updated_at = now()
          returning id, class_id as "classId", user_id as "userId", member_type as "memberType", status
          `,
          [auth.tenantId, classId, userId, memberType, status, jsonBodyValue(assignment.metadata), auth.userId],
        );
        items.push({ index, ...item.rows[0] });
      } catch (error) {
        errors.push({
          index,
          code: error instanceof HttpError ? error.code : 'CLASS_BULK_ASSIGN_ITEM_FAILED',
          message: error instanceof Error ? error.message : 'Class assignment item failed',
        });
      }
    }

    await recordAudit(client, auth, 'tenant.class_members.bulk_assigned', 'tenant_class_members', classId, {
      classId,
      total: assignments.length,
      successCount: items.length,
      errorCount: errors.length,
    });
    return { total: assignments.length, successCount: items.length, errorCount: errors.length, items, errors };
  });

  return result;
}

export async function tenantTeachersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'classes:read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const keyword = stringParam(ctx, 'keyword');
  const params: unknown[] = [auth.tenantId];
  const filters = ['tm.tenant_id = $1', `tm.role = 'teacher'`, `tm.status = 'active'`];
  if (keyword) {
    params.push(`%${keyword}%`);
    filters.push(`(u.username ilike $${params.length} or u.name ilike $${params.length} or u.phone ilike $${params.length})`);
  }
  if (!canReadAllClassScope(auth)) {
    params.push(auth.userId);
    filters.push(`tm.user_id = $${params.length}::uuid`);
  }
  params.push(limit);

  const items = await query<Record<string, unknown>>(
    `
    select tm.id as "membershipId", tm.user_id as "userId", tm.status,
           u.username, u.email::text as email, u.phone, u.name,
           u.avatar_url as "avatarUrl", u.last_seen_at as "lastSeenAt",
           coalesce(count(tcm.id) filter (where tcm.status = 'active'), 0)::int as "classCount"
    from public.tenant_memberships tm
    join public.platform_users u on u.id = tm.user_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 in ('teacher', 'assistant', 'head_teacher')
    where ${filters.join(' and ')}
    group by tm.id, u.id
    order by tm.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function tenantStudentNotesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:notes:read');
  const studentUserId = stringParam(ctx, 'studentUserId');
  if (!studentUserId) throw new HttpError(400, 'studentUserId is required', 'STUDENT_USER_ID_REQUIRED');
  await ensureStudentInScope(auth, studentUserId);
  const limit = intParam(ctx, 'limit', 100, 300);

  const items = await query<Record<string, unknown>>(
    `
    select sn.id, sn.student_user_id as "studentUserId", sn.note_type as "noteType",
           sn.content, sn.visibility, sn.is_pinned as "isPinned", sn.metadata,
           sn.created_by as "createdBy", sn.updated_by as "updatedBy",
           sn.created_at as "createdAt", sn.updated_at as "updatedAt",
           creator.name as "createdByName", creator.username as "createdByUsername"
    from public.tenant_student_notes sn
    left join public.platform_users creator on creator.id = sn.created_by
    where sn.tenant_id = $1
      and sn.student_user_id = $2
      and (
        sn.visibility <> 'author_only'
        or sn.created_by = $3
        or $4::boolean
      )
    order by sn.is_pinned desc, sn.created_at desc
    limit $5
    `,
    [auth.tenantId, studentUserId, auth.userId, canReadAllClassScope(auth), limit],
  );
  return { items };
}

export async function upsertTenantStudentNoteRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:notes:write');
  const body = await readJsonBody(ctx);
  const studentUserId = requiredString(body, 'studentUserId');
  await ensureStudentInScope(auth, studentUserId);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.tenant_student_notes (
        id, tenant_id, student_user_id, note_type, content, visibility,
        is_pinned, metadata, created_by, updated_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6,
        $7, $8::jsonb, $9, $9
      )
      on conflict (id)
      do update set note_type = excluded.note_type,
                    content = excluded.content,
                    visibility = excluded.visibility,
                    is_pinned = excluded.is_pinned,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.tenant_student_notes.tenant_id = excluded.tenant_id
        and public.tenant_student_notes.student_user_id = excluded.student_user_id
        and (
          public.tenant_student_notes.created_by = $9
          or $10::boolean
        )
      returning id, student_user_id as "studentUserId", note_type as "noteType",
                content, visibility, is_pinned as "isPinned", metadata,
                created_by as "createdBy", updated_by as "updatedBy",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        studentUserId,
        optionalChoice(body.noteType, STUDENT_NOTE_TYPES, 'general'),
        requiredString(body, 'content'),
        optionalChoice(body.visibility, STUDENT_NOTE_VISIBILITIES, 'tenant_staff'),
        boolValue(body.isPinned, false),
        jsonBodyValue(body.metadata),
        auth.userId,
        canReadAllClassScope(auth),
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Student note not found or not editable', 'STUDENT_NOTE_NOT_EDITABLE');
    await recordAudit(client, auth, 'tenant.student_note.upserted', 'tenant_student_notes', result.rows[0].id, {
      studentUserId,
      noteType: result.rows[0].noteType,
    });
    return result.rows[0];
  });

  return { item };
}

export async function tenantStudentFollowupsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:followups:read');
  const limit = intParam(ctx, 'limit', 100, 300);
  const studentUserId = stringParam(ctx, 'studentUserId');
  const status = stringParam(ctx, 'status');
  const assignedTo = stringParam(ctx, 'assignedToUserId');
  const scopedIds = await scopedClassIds(auth);
  if (studentUserId) await ensureStudentInScope(auth, studentUserId);
  if (status && !STUDENT_FOLLOWUP_STATUSES.includes(status)) {
    throw new HttpError(400, `Invalid follow-up status: ${status}`, 'INVALID_FOLLOWUP_STATUS');
  }

  const params: unknown[] = [auth.tenantId];
  const filters = ['sf.tenant_id = $1'];
  if (studentUserId) {
    params.push(studentUserId);
    filters.push(`sf.student_user_id = $${params.length}::uuid`);
  }
  if (status) {
    params.push(status);
    filters.push(`sf.status = $${params.length}`);
  }
  if (assignedTo) {
    params.push(assignedTo);
    filters.push(`sf.assigned_to_user_id = $${params.length}::uuid`);
  }
  if (scopedIds) {
    params.push(scopedIds);
    filters.push(`exists (
      select 1 from public.tenant_class_members scoped_cm
      where scoped_cm.tenant_id = sf.tenant_id
        and scoped_cm.user_id = sf.student_user_id
        and scoped_cm.class_id = any($${params.length}::uuid[])
        and scoped_cm.member_type = 'student'
        and scoped_cm.status = 'active'
    )`);
  }
  params.push(limit);

  const items = await query<Record<string, unknown>>(
    `
    select sf.id, sf.student_user_id as "studentUserId",
           student.name as "studentName", student.phone as "studentPhone",
           sf.assigned_to_user_id as "assignedToUserId", assignee.name as "assignedToName",
           sf.class_id as "classId", tc.name as "className",
           sf.title, sf.description, sf.followup_type as "followupType",
           sf.priority, sf.status, sf.due_at as "dueAt",
           sf.completed_at as "completedAt", sf.completed_by as "completedBy",
           sf.metadata, sf.created_by as "createdBy", sf.updated_by as "updatedBy",
           sf.created_at as "createdAt", sf.updated_at as "updatedAt"
    from public.tenant_student_followups sf
    left join public.platform_users student on student.id = sf.student_user_id
    left join public.platform_users assignee on assignee.id = sf.assigned_to_user_id
    left join public.tenant_classes tc on tc.tenant_id = sf.tenant_id and tc.id = sf.class_id
    where ${filters.join(' and ')}
    order by case sf.priority
      when 'urgent' then 1
      when 'high' then 2
      when 'normal' then 3
      else 4
    end, sf.due_at asc nulls last, sf.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items: items.map(item => maskStudentFields(auth, item)) };
}

export async function tenantStudentFollowupReportRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:followups:read');
  const range = parseFollowupReportRange(
    stringParam(ctx, 'timeRange'),
    stringParam(ctx, 'startDate'),
    stringParam(ctx, 'endDate'),
  );
  const limit = intParam(ctx, 'limit', 10, 50);
  const assignedTo = stringParam(ctx, 'assignedToUserId');
  const followupType = stringParam(ctx, 'followupType');
  const classId = stringParam(ctx, 'classId');
  const scopedIds = await scopedClassIds(auth);
  if (classId) await ensureReadableClass(auth, classId);
  if (followupType && !STUDENT_FOLLOWUP_TYPES.includes(followupType)) {
    throw new HttpError(400, `Invalid follow-up type: ${followupType}`, 'INVALID_FOLLOWUP_TYPE');
  }

  const params: unknown[] = [auth.tenantId, range.startAt, range.endAt];
  const filters = ['sf.tenant_id = $1', '$2::timestamptz <= $3::timestamptz'];
  if (assignedTo) {
    params.push(assignedTo);
    filters.push(`sf.assigned_to_user_id = $${params.length}::uuid`);
  }
  if (followupType) {
    params.push(followupType);
    filters.push(`sf.followup_type = $${params.length}`);
  }
  if (classId) {
    params.push(classId);
    filters.push(`sf.class_id = $${params.length}::uuid`);
  }
  if (scopedIds) {
    params.push(scopedIds);
    filters.push(`exists (
      select 1 from public.tenant_class_members scoped_cm
      where scoped_cm.tenant_id = sf.tenant_id
        and scoped_cm.user_id = sf.student_user_id
        and scoped_cm.class_id = any($${params.length}::uuid[])
        and scoped_cm.member_type = 'student'
        and scoped_cm.status = 'active'
    )`);
  }
  const whereSql = filters.join(' and ');
  const limitedParams = [...params, limit];

  const [
    summary,
    byStatus,
    byType,
    byPriority,
    byAssignee,
    byClass,
    dailyTrend,
    overdueItems,
    crmQueueSummary,
  ] = await Promise.all([
    query<Record<string, unknown>>(
      `
      select
        count(*) filter (where sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as total,
        count(*) filter (where sf.status = 'open' and sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as open,
        count(*) filter (where sf.status = 'in_progress' and sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as "inProgress",
        count(*) filter (where sf.status = 'done' and sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as done,
        count(*) filter (where sf.status = 'cancelled' and sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as cancelled,
        count(*) filter (where sf.priority in ('high', 'urgent') and sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as "highPriority",
        count(*) filter (where sf.metadata->'crmPush' is not null and sf.created_at >= $2::timestamptz and sf.created_at < $3::timestamptz)::int as "crmPushTasks",
        count(*) filter (where sf.due_at is not null and sf.due_at < now() and sf.status in ('open', 'in_progress'))::int as "overdueBacklog",
        count(*) filter (where sf.status in ('open', 'in_progress'))::int as "openBacklog",
        count(*) filter (where sf.completed_at >= $2::timestamptz and sf.completed_at < $3::timestamptz)::int as "completedInRange",
        coalesce(avg(extract(epoch from (sf.completed_at - sf.created_at)) / 3600) filter (
          where sf.completed_at >= $2::timestamptz and sf.completed_at < $3::timestamptz and sf.completed_at is not null
        ), 0)::numeric as "avgCompleteHours"
      from public.tenant_student_followups sf
      where ${whereSql}
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select sf.status, count(*)::int as count
      from public.tenant_student_followups sf
      where ${whereSql}
        and sf.created_at >= $2::timestamptz
        and sf.created_at < $3::timestamptz
      group by sf.status
      order by case sf.status
        when 'open' then 1
        when 'in_progress' then 2
        when 'done' then 3
        when 'cancelled' then 4
        else 9
      end
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select sf.followup_type as "followupType", count(*)::int as count
      from public.tenant_student_followups sf
      where ${whereSql}
        and sf.created_at >= $2::timestamptz
        and sf.created_at < $3::timestamptz
      group by sf.followup_type
      order by count desc, "followupType" asc
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select sf.priority, count(*)::int as count
      from public.tenant_student_followups sf
      where ${whereSql}
        and sf.created_at >= $2::timestamptz
        and sf.created_at < $3::timestamptz
      group by sf.priority
      order by case sf.priority
        when 'urgent' then 1
        when 'high' then 2
        when 'normal' then 3
        when 'low' then 4
        else 9
      end
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select sf.assigned_to_user_id as "assignedToUserId",
             coalesce(assignee.name, assignee.username, '未指派') as "assignedToName",
             tm.role as "assignedToRole",
             count(*)::int as total,
             count(*) filter (where sf.status in ('open', 'in_progress'))::int as open,
             count(*) filter (where sf.status = 'done')::int as done,
             count(*) filter (where sf.due_at is not null and sf.due_at < now() and sf.status in ('open', 'in_progress'))::int as overdue,
             count(*) filter (where sf.metadata->'crmPush' is not null)::int as "crmPushTasks",
             coalesce(avg(extract(epoch from (sf.completed_at - sf.created_at)) / 3600) filter (where sf.completed_at is not null), 0)::numeric as "avgCompleteHours",
             max(coalesce(sf.completed_at, sf.updated_at, sf.created_at)) as "latestActivityAt"
      from public.tenant_student_followups sf
      left join public.platform_users assignee on assignee.id = sf.assigned_to_user_id
      left join public.tenant_memberships tm on tm.tenant_id = sf.tenant_id
        and tm.user_id = sf.assigned_to_user_id
        and tm.status = 'active'
        and tm.role = any($${limitedParams.length + 1}::text[])
      where ${whereSql}
        and sf.created_at >= $2::timestamptz
        and sf.created_at < $3::timestamptz
      group by sf.assigned_to_user_id, assignee.name, assignee.username, tm.role
      order by total desc, overdue desc, "latestActivityAt" desc
      limit $${limitedParams.length}
      `,
      [...limitedParams, CRM_ASSIGNABLE_ROLES],
    ),
    query<Record<string, unknown>>(
      `
      select sf.class_id as "classId",
             coalesce(tc.name, '未绑定班级') as "className",
             tc.code as "classCode",
             count(*)::int as total,
             count(*) filter (where sf.status in ('open', 'in_progress'))::int as open,
             count(*) filter (where sf.status = 'done')::int as done,
             count(*) filter (where sf.due_at is not null and sf.due_at < now() and sf.status in ('open', 'in_progress'))::int as overdue,
             max(coalesce(sf.completed_at, sf.updated_at, sf.created_at)) as "latestActivityAt"
      from public.tenant_student_followups sf
      left join public.tenant_classes tc on tc.tenant_id = sf.tenant_id and tc.id = sf.class_id
      where ${whereSql}
        and sf.created_at >= $2::timestamptz
        and sf.created_at < $3::timestamptz
      group by sf.class_id, tc.name, tc.code
      order by total desc, overdue desc, "latestActivityAt" desc
      limit $${limitedParams.length}
      `,
      limitedParams,
    ),
    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 (sf.created_at at time zone 'Asia/Shanghai')::date::text as date,
               count(*)::int as created,
               count(*) filter (where sf.metadata->'crmPush' is not null)::int as "crmPushCreated",
               count(*) filter (where sf.priority in ('high', 'urgent'))::int as "highPriority"
        from public.tenant_student_followups sf
        where ${whereSql}
          and sf.created_at >= $2::timestamptz
          and sf.created_at < $3::timestamptz
        group by (sf.created_at at time zone 'Asia/Shanghai')::date
      ),
      completed_daily as (
        select (sf.completed_at at time zone 'Asia/Shanghai')::date::text as date,
               count(*)::int as completed
        from public.tenant_student_followups sf
        where ${whereSql}
          and sf.completed_at >= $2::timestamptz
          and sf.completed_at < $3::timestamptz
          and sf.status = 'done'
        group by (sf.completed_at at time zone 'Asia/Shanghai')::date
      )
      select (d.day_start at time zone 'Asia/Shanghai')::date::text as date,
             coalesce(cd.created, 0)::int as created,
             coalesce(cd."crmPushCreated", 0)::int as "crmPushCreated",
             coalesce(cd."highPriority", 0)::int as "highPriority",
             coalesce(done.completed, 0)::int as completed
      from days d
      left join created_daily cd on cd.date = (d.day_start at time zone 'Asia/Shanghai')::date::text
      left join completed_daily done on done.date = (d.day_start at time zone 'Asia/Shanghai')::date::text
      order by date asc
      `,
      params,
    ),
    query<Record<string, unknown>>(
      `
      select sf.id, sf.student_user_id as "studentUserId",
             student.name as "studentName", student.phone as "studentPhone",
             sf.assigned_to_user_id as "assignedToUserId",
             coalesce(assignee.name, assignee.username) as "assignedToName",
             sf.class_id as "classId", tc.name as "className",
             sf.title, sf.followup_type as "followupType", sf.priority,
             sf.status, sf.due_at as "dueAt",
             extract(epoch from (now() - sf.due_at)) / 3600 as "overdueHours",
             sf.created_at as "createdAt", sf.updated_at as "updatedAt"
      from public.tenant_student_followups sf
      left join public.platform_users student on student.id = sf.student_user_id
      left join public.platform_users assignee on assignee.id = sf.assigned_to_user_id
      left join public.tenant_classes tc on tc.tenant_id = sf.tenant_id and tc.id = sf.class_id
      where ${whereSql}
        and sf.due_at is not null
        and sf.due_at < now()
        and sf.status in ('open', 'in_progress')
      order by case sf.priority
        when 'urgent' then 1
        when 'high' then 2
        when 'normal' then 3
        else 4
      end, sf.due_at asc
      limit $${limitedParams.length}
      `,
      limitedParams,
    ),
    query<Record<string, unknown>>(
      `
      select
        count(*) filter (where q.created_at >= $2::timestamptz and q.created_at < $3::timestamptz)::int as total,
        count(*) filter (where q.status in ('pending', 'retrying', 'processing') and q.created_at >= $2::timestamptz and q.created_at < $3::timestamptz)::int as pending,
        count(*) filter (where q.status = 'sent' and q.created_at >= $2::timestamptz and q.created_at < $3::timestamptz)::int as sent,
        count(*) filter (where q.status in ('failed', 'discarded') and q.created_at >= $2::timestamptz and q.created_at < $3::timestamptz)::int as failed,
        count(distinct q.record_id) filter (where q.created_at >= $2::timestamptz and q.created_at < $3::timestamptz)::int as "studentCount"
      from public.crm_webhook_queue q
      join public.tenant_student_followups sf on sf.tenant_id = q.tenant_id
        and sf.metadata->'crmPush'->>'idempotencyKey' = q.idempotency_key
      where q.tenant_id = $1
        and q.source = 'tenant.student.crm_push'
        and ${whereSql}
      `,
      params,
    ),
  ]);

  const summaryRow = summary[0] || {};
  const total = intValue(summaryRow.total, 0);
  const done = intValue(summaryRow.done, 0);
  const completedInRange = intValue(summaryRow.completedInRange, 0);
  return {
    item: {
      range,
      filters: {
        assignedToUserId: assignedTo || null,
        followupType: followupType || null,
        classId: classId || null,
        scoped: scopedIds !== null,
      },
      summary: {
        total,
        open: intValue(summaryRow.open, 0),
        inProgress: intValue(summaryRow.inProgress, 0),
        done,
        cancelled: intValue(summaryRow.cancelled, 0),
        highPriority: intValue(summaryRow.highPriority, 0),
        crmPushTasks: intValue(summaryRow.crmPushTasks, 0),
        openBacklog: intValue(summaryRow.openBacklog, 0),
        overdueBacklog: intValue(summaryRow.overdueBacklog, 0),
        completedInRange,
        completionRate: ratio(done, total),
        completedInRangeRate: ratio(completedInRange, total),
        avgCompleteHours: Number(numberValue(summaryRow.avgCompleteHours, 0).toFixed(2)),
      },
      crmQueue: crmQueueSummary[0] || { total: 0, pending: 0, sent: 0, failed: 0, studentCount: 0 },
      byStatus,
      byType,
      byPriority,
      byAssignee: byAssignee.map(item => ({
        ...item,
        avgCompleteHours: Number(numberValue(item.avgCompleteHours, 0).toFixed(2)),
        completionRate: ratio(intValue(item.done, 0), intValue(item.total, 0)),
      })),
      byClass,
      dailyTrend,
      overdueItems: overdueItems.map(item => maskStudentFields(auth, {
        ...item,
        overdueHours: Number(numberValue(item.overdueHours, 0).toFixed(2)),
      })),
    },
  };
}

export async function tenantStudentSupervisionPreviewRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:read');
  requireTenantPermission(auth, 'students:supervision:read');
  const limit = intParam(ctx, 'limit', 20, 100);
  const classId = stringParam(ctx, 'classId') || null;
  const assignedToUserId = stringParam(ctx, 'assignedToUserId') || null;
  if (classId) await ensureReadableSupervisionClass(auth, classId);
  if (assignedToUserId) {
    await query(
      `
      select 1
      from public.tenant_memberships
      where tenant_id = $1 and user_id = $2::uuid and status = 'active'
        and role = any($3::text[])
      limit 1
      `,
      [auth.tenantId, assignedToUserId, CRM_ASSIGNABLE_ROLES],
    ).then(rows => {
      if (!rows[0]) throw new HttpError(400, 'User is not an active tenant member', 'ASSIGNEE_NOT_FOUND');
    });
  }

  const rules = parseSupervisionRules({
    windowDays: stringParam(ctx, 'windowDays'),
    inactivityDays: stringParam(ctx, 'inactivityDays'),
    minAnswers: stringParam(ctx, 'minAnswers'),
    lowAccuracyThreshold: stringParam(ctx, 'lowAccuracyThreshold'),
    wrongQuestionThreshold: stringParam(ctx, 'wrongQuestionThreshold'),
    vocabularyDueThreshold: stringParam(ctx, 'vocabularyDueThreshold'),
    staleSessionDays: stringParam(ctx, 'staleSessionDays'),
  });
  const candidates = await loadStudentSupervisionCandidates(auth, { rules, classId, assignedToUserId, limit });
  return {
    item: {
      rules,
      filters: {
        classId,
        assignedToUserId,
        scoped: (await scopedSupervisionClassIds(auth)) !== null,
      },
      totalCandidates: candidates.length,
      candidates,
    },
  };
}

export async function tenantStudentSupervisionRulesRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:supervision:read');
  const limit = intParam(ctx, 'limit', 50, 200);
  const status = stringParam(ctx, 'status');
  const params: unknown[] = [auth.tenantId];
  const filters = ['sr.tenant_id = $1'];

  if (status) {
    if (!STUDENT_SUPERVISION_RULE_STATUSES.includes(status)) {
      throw new HttpError(400, `Invalid supervision rule status: ${status}`, 'INVALID_SUPERVISION_RULE_STATUS');
    }
    params.push(status);
    filters.push(`sr.status = $${params.length}`);
  } else {
    filters.push(`sr.status <> 'archived'`);
  }

  const scopedIds = await scopedSupervisionClassIds(auth);
  if (scopedIds) {
    params.push(scopedIds);
    filters.push(`sr.class_id = any($${params.length}::uuid[])`);
  }
  params.push(limit);

  const items = await query(
    `
    select sr.id, sr.name, sr.status, sr.rules, sr.schedule,
           sr.class_id as "classId", tc.name as "className", tc.code as "classCode",
           sr.assigned_to_user_id as "assignedToUserId", u.name as "assignedToName", u.username as "assignedToUsername",
           sr.limit_count as "limit", sr.metadata,
           sr.last_run_at as "lastRunAt", sr.next_run_at as "nextRunAt", sr.last_result as "lastResult",
           sr.created_by as "createdBy", sr.updated_by as "updatedBy",
           sr.created_at as "createdAt", sr.updated_at as "updatedAt"
    from public.tenant_student_supervision_rules sr
    left join public.tenant_classes tc on tc.tenant_id = sr.tenant_id and tc.id = sr.class_id
    left join public.platform_users u on u.id = sr.assigned_to_user_id
    where ${filters.join(' and ')}
    order by case sr.status when 'active' then 1 when 'disabled' then 2 else 3 end,
             sr.next_run_at asc nulls last, sr.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items, scoped: scopedIds !== null };
}

export async function upsertTenantStudentSupervisionRuleRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:supervision:write');
  const body = await readJsonBody(ctx);
  const id = nullableString(body.id);
  const classId = nullableString(body.classId);
  const assignedToUserId = nullableString(body.assignedToUserId);
  const status = optionalChoice(body.status, STUDENT_SUPERVISION_RULE_STATUSES, 'active');
  const rules = parseSupervisionRules(body.rules);
  const schedule = parseSupervisionSchedule(body.schedule);
  const limit = boundedIntValue(body.limit, 20, 1, MAX_SUPERVISION_GENERATE);

  if (classId) {
    await ensureReadableSupervisionClass(auth, classId);
  } else if ((await scopedSupervisionClassIds(auth)) !== null) {
    throw new HttpError(403, 'Scoped members must bind supervision rules to a readable class', 'CLASS_SCOPE_REQUIRED');
  }

  const item = await transaction(async client => {
    await ensureUserTenantMembership(client, auth.tenantId, assignedToUserId, CRM_ASSIGNABLE_ROLES, 'ASSIGNEE_NOT_FOUND');
    const nextRunAt = status === 'active' ? nextSupervisionRunAt(schedule) : null;
    const result = await client.query(
      `
      insert into public.tenant_student_supervision_rules (
        id, tenant_id, name, status, rules, schedule, class_id,
        assigned_to_user_id, limit_count, metadata, next_run_at,
        created_by, updated_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5::jsonb, $6::jsonb, $7::uuid,
        $8::uuid, $9, $10::jsonb, $11::timestamptz,
        $12, $12
      )
      on conflict (id)
      do update set name = excluded.name,
                    status = excluded.status,
                    rules = excluded.rules,
                    schedule = excluded.schedule,
                    class_id = excluded.class_id,
                    assigned_to_user_id = excluded.assigned_to_user_id,
                    limit_count = excluded.limit_count,
                    metadata = excluded.metadata,
                    next_run_at = excluded.next_run_at,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.tenant_student_supervision_rules.tenant_id = excluded.tenant_id
      returning id, name, status, rules, schedule,
                class_id as "classId", assigned_to_user_id as "assignedToUserId",
                limit_count as "limit", metadata,
                last_run_at as "lastRunAt", next_run_at as "nextRunAt", last_result as "lastResult",
                created_by as "createdBy", updated_by as "updatedBy",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        id,
        requiredLimitedString(body, 'name', 120),
        status,
        JSON.stringify(rules),
        JSON.stringify(schedule),
        classId,
        assignedToUserId,
        limit,
        JSON.stringify(objectValue(body.metadata)),
        nextRunAt,
        auth.userId,
      ],
    );

    if (!result.rows[0]) {
      throw new HttpError(404, 'Supervision rule not found for this tenant', 'SUPERVISION_RULE_NOT_FOUND');
    }

    await recordAudit(client, auth, 'tenant.students.supervision_rule_upserted', 'tenant_student_supervision_rules', result.rows[0].id, {
      name: result.rows[0].name,
      status,
      classId,
      assignedToUserId,
      nextRunAt,
    });

    return result.rows[0];
  });

  return { item };
}

export async function generateTenantStudentSupervisionRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:read');
  requireTenantPermission(auth, 'students:supervision:write');
  const body = await readJsonBody(ctx);
  const limit = boundedIntValue(body.limit, 20, 1, MAX_SUPERVISION_GENERATE);
  const classId = nullableString(body.classId);
  const assignedToUserId = nullableString(body.assignedToUserId);
  const dueAt = optionalTimestampString(body.dueAt, 'dueAt') || defaultSupervisionDueAt();
  const batchKey = supervisionBatchKey(body.batchKey);
  const rules = parseSupervisionRules(body.rules);
  const onlyStudentUserIds = Array.isArray(body.studentUserIds)
    ? strictUuidArrayValue(body.studentUserIds, 'studentUserIds', MAX_SUPERVISION_GENERATE)
    : [];
  if (classId) await ensureReadableSupervisionClass(auth, classId);

  const result = await generateStudentSupervisionFollowups(auth, {
    rules,
    classId,
    assignedToUserId,
    studentUserIds: onlyStudentUserIds,
    dueAt,
    batchKey,
    limit,
    metadata: objectValue(body.metadata),
  });

  return result;
}

export async function upsertTenantStudentFollowupRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'students:followups:write');
  const body = await readJsonBody(ctx);
  const studentUserId = requiredString(body, 'studentUserId');
  await ensureStudentInScope(auth, studentUserId);
  const requestedClassId = nullableString(body.classId);
  if (requestedClassId) await ensureReadableClass(auth, requestedClassId);

  const item = await transaction(async client => {
    const classId = requestedClassId;
    const assignedToUserId = nullableString(body.assignedToUserId);
    if (classId) await ensureTenantClass(client, auth.tenantId, classId);
    await ensureUserTenantMembership(
      client,
      auth.tenantId,
      assignedToUserId,
      ['tenant_owner', 'tenant_admin', 'tenant_operator', 'teacher', 'sales', 'agent'],
      'ASSIGNEE_NOT_FOUND',
    );
    const status = optionalChoice(body.status, STUDENT_FOLLOWUP_STATUSES, 'open');
    const completedAt = status === 'done' ? nullableString(body.completedAt) || new Date().toISOString() : null;
    const completedBy = status === 'done' ? auth.userId : null;

    const result = await client.query(
      `
      insert into public.tenant_student_followups (
        id, tenant_id, student_user_id, assigned_to_user_id, class_id,
        title, description, followup_type, priority, status, due_at,
        completed_at, completed_by, metadata, created_by, updated_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4::uuid, $5::uuid,
        $6, $7, $8, $9, $10, $11::timestamptz,
        $12::timestamptz, $13::uuid, $14::jsonb, $15, $15
      )
      on conflict (id)
      do update set assigned_to_user_id = excluded.assigned_to_user_id,
                    class_id = excluded.class_id,
                    title = excluded.title,
                    description = excluded.description,
                    followup_type = excluded.followup_type,
                    priority = excluded.priority,
                    status = excluded.status,
                    due_at = excluded.due_at,
                    completed_at = case
                      when excluded.status = 'done' then coalesce(excluded.completed_at, public.tenant_student_followups.completed_at, now())
                      else null
                    end,
                    completed_by = case
                      when excluded.status = 'done' then coalesce(excluded.completed_by, public.tenant_student_followups.completed_by)
                      else null
                    end,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.tenant_student_followups.tenant_id = excluded.tenant_id
        and public.tenant_student_followups.student_user_id = excluded.student_user_id
      returning id, student_user_id as "studentUserId",
                assigned_to_user_id as "assignedToUserId", class_id as "classId",
                title, description, followup_type as "followupType", priority, status,
                due_at as "dueAt", completed_at as "completedAt", completed_by as "completedBy",
                metadata, created_by as "createdBy", updated_by as "updatedBy",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        nullableString(body.id),
        studentUserId,
        assignedToUserId,
        classId,
        requiredString(body, 'title'),
        nullableString(body.description),
        optionalChoice(body.followupType, STUDENT_FOLLOWUP_TYPES, 'learning'),
        optionalChoice(body.priority, STUDENT_FOLLOWUP_PRIORITIES, 'normal'),
        status,
        nullableString(body.dueAt),
        completedAt,
        completedBy,
        jsonBodyValue(body.metadata),
        auth.userId,
      ],
    );
    if (!result.rows[0]) throw new HttpError(404, 'Student follow-up not found', 'STUDENT_FOLLOWUP_NOT_FOUND');
    await recordAudit(client, auth, 'tenant.student_followup.upserted', 'tenant_student_followups', result.rows[0].id, {
      studentUserId,
      status,
      assignedToUserId,
      classId,
    });
    return result.rows[0];
  });

  return { item };
}

export async function pushTenantStudentsToCrmRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requireTenantPermission(auth, 'crm:write');
  requireTenantPermission(auth, 'students:read');
  requireTenantPermission(auth, 'students:followups:write');
  const body = await readJsonBody(ctx);

  const studentUserIds = strictUuidArrayValue(body.studentUserIds || body.userIds, 'studentUserIds', MAX_BULK_CRM_PUSH);
  const classId = nullableString(body.classId);
  if (classId) await ensureReadableClass(auth, classId);

  const source = limitedNullableString(body.source, 80, 'source') || 'manual_batch';
  const title = requiredLimitedString(body, 'title', 120);
  const message = limitedNullableString(body.message ?? body.description, 1000, 'message');
  const followupType = optionalChoice(body.followupType, STUDENT_FOLLOWUP_TYPES, 'sales');
  const priority = optionalChoice(body.priority, STUDENT_FOLLOWUP_PRIORITIES, 'normal');
  const assignedToUserId = nullableString(body.assignedToUserId);
  const dueAt = optionalTimestampString(body.dueAt, 'dueAt');
  const metadata = objectValue(body.metadata);
  const requestId = randomUUID();
  const idempotencyInput = limitedNullableString(body.idempotencyKey, 160, 'idempotencyKey');
  const includePhone = canSeeStudentPhone(auth);

  const result = await transaction(async client => {
    const config = await loadCrmPushConfig(client, auth.tenantId);
    if (classId) await ensureTenantClass(client, auth.tenantId, classId);
    await ensureUserTenantMembership(client, auth.tenantId, assignedToUserId, CRM_ASSIGNABLE_ROLES, 'ASSIGNEE_NOT_FOUND');
    const assignee = await loadCrmAssigneePayload(client, auth.tenantId, assignedToUserId);
    const classPayload = await loadCrmClassPayload(client, auth.tenantId, classId);
    const delaySeconds = Math.max(0, Number(config.delaySec ?? 60));
    const items: unknown[] = [];
    const errors: unknown[] = [];

    for (let index = 0; index < studentUserIds.length; index += 1) {
      const studentUserId = studentUserIds[index];
      try {
        await ensureStudentInScope(auth, studentUserId);
        await ensureStudentInClass(client, auth.tenantId, studentUserId, classId);
        const student = await loadStudentCrmPayload(client, auth, studentUserId, includePhone);
        const idempotencyKey = crmPushIdempotencyKey(idempotencyInput, requestId, studentUserId);
        const followupMetadata = {
          ...metadata,
          crmPush: {
            requestId,
            idempotencyKey,
            source,
          },
        };

        let followup = (await client.query<{
          id: string;
          studentUserId: string;
          assignedToUserId: string | null;
          classId: string | null;
          title: string;
          dueAt: string | null;
          createdAt: string;
        }>(
          `
          select id, student_user_id as "studentUserId",
                 assigned_to_user_id as "assignedToUserId", class_id as "classId",
                 title, due_at as "dueAt", created_at as "createdAt"
          from public.tenant_student_followups
          where tenant_id = $1
            and student_user_id = $2::uuid
            and metadata->'crmPush'->>'idempotencyKey' = $3
          limit 1
          `,
          [auth.tenantId, studentUserId, idempotencyKey],
        )).rows[0];

        if (!followup) {
          followup = (await client.query(
            `
            insert into public.tenant_student_followups (
              tenant_id, student_user_id, assigned_to_user_id, class_id,
              title, description, followup_type, priority, status, due_at,
              metadata, created_by, updated_by
            )
            values (
              $1, $2::uuid, $3::uuid, $4::uuid,
              $5, $6, $7, $8, 'open', $9::timestamptz,
              $10::jsonb, $11, $11
            )
            returning id, student_user_id as "studentUserId",
                      assigned_to_user_id as "assignedToUserId", class_id as "classId",
                      title, due_at as "dueAt", created_at as "createdAt"
            `,
            [
              auth.tenantId,
              studentUserId,
              assignedToUserId,
              classId,
              title,
              message,
              followupType,
              priority,
              dueAt,
              JSON.stringify(followupMetadata),
              auth.userId,
            ],
          )).rows[0];
        }

        const payload = {
          eventType: 'student.crm_push',
          formName: config.formName || '刷题题库',
          examType: config.examType || '成人本科',
          source,
          title,
          message,
          student,
          assignee,
          class: classPayload,
          followup: {
            id: followup.id,
            title: followup.title,
            followupType,
            priority,
            dueAt: followup.dueAt,
          },
          actor: {
            userId: auth.userId,
            role: auth.role,
          },
          metadata,
        };

        const queued = await client.query<{
          id: string;
          status: string;
          scheduledAt: string | null;
          idempotencyKey: string;
        }>(
          `
          insert into public.crm_webhook_queue (
            tenant_id, record_id, status, scheduled_at, next_attempt_at, lead_id,
            source, payload, idempotency_key, target_url
          )
          values ($1, $2, 'pending', now() + ($3::int * interval '1 second'), null, null, $4, $5::jsonb, $6, $7)
          on conflict (tenant_id, idempotency_key) where idempotency_key is not null
          do update set status = case
                          when public.crm_webhook_queue.status = 'sent' then public.crm_webhook_queue.status
                          else 'pending'
                        end,
                        scheduled_at = excluded.scheduled_at,
                        next_attempt_at = null,
                        payload = excluded.payload,
                        target_url = excluded.target_url,
                        updated_at = now()
          returning id, status, scheduled_at as "scheduledAt", idempotency_key as "idempotencyKey"
          `,
          [
            auth.tenantId,
            studentUserId,
            delaySeconds,
            'tenant.student.crm_push',
            JSON.stringify(payload),
            idempotencyKey,
            config.url,
          ],
        );

        items.push({
          index,
          studentUserId,
          followupId: followup.id,
          queueId: queued.rows[0].id,
          queueStatus: queued.rows[0].status,
          scheduledAt: queued.rows[0].scheduledAt,
        });
      } catch (error) {
        errors.push({
          index,
          studentUserId,
          code: error instanceof HttpError ? error.code : 'STUDENT_CRM_PUSH_ITEM_FAILED',
          message: error instanceof Error ? error.message : 'Student CRM push item failed',
        });
      }
    }

    await recordAudit(client, auth, 'tenant.students.crm_pushed', 'crm_webhook_queue', null, {
      requestId,
      total: studentUserIds.length,
      successCount: items.length,
      errorCount: errors.length,
      source,
      followupType,
      priority,
      assignedToUserId,
      classId,
    });

    return {
      requestId,
      total: studentUserIds.length,
      successCount: items.length,
      errorCount: errors.length,
      items,
      errors,
    };
  });

  return result;
}
