import { createHash } from 'node:crypto';
import type pg from 'pg';
import { HttpError } from '../../core/http.js';
import { query, transaction } from '../../core/db.js';
import { hasTenantPermission, type TenantAdminAuth } from './auth.js';

export const MAX_SUPERVISION_GENERATE = 100;
export const CRM_ASSIGNABLE_ROLES = ['tenant_owner', 'tenant_admin', 'tenant_operator', 'teacher', 'sales', 'agent'];

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

const SUPERVISION_RULE_DEFAULTS = {
  windowDays: 14,
  inactivityDays: 7,
  minAnswers: 10,
  lowAccuracyThreshold: 0.6,
  wrongQuestionThreshold: 5,
  vocabularyDueThreshold: 20,
  staleSessionDays: 3,
};

export interface SupervisionRuleConfig {
  windowDays: number;
  inactivityDays: number;
  minAnswers: number;
  lowAccuracyThreshold: number;
  wrongQuestionThreshold: number;
  vocabularyDueThreshold: number;
  staleSessionDays: number;
}

export interface SupervisionScheduleConfig {
  enabled: boolean;
  frequency: 'manual' | 'daily' | 'weekly';
  hour: number;
  minute: number;
  timezone: 'Asia/Shanghai';
  weekdays: number[];
}

export interface SupervisionAuthContext {
  tenantId: string;
  userId: string | null;
  role: string;
  permissions: Record<string, unknown>;
  templatePermissions: Record<string, unknown>;
  fieldPermissions: Record<string, unknown>;
  dataScope: Record<string, unknown>;
}

export interface StudentSupervisionGenerateInput {
  rules: SupervisionRuleConfig;
  classId: string | null;
  assignedToUserId: string | null;
  studentUserIds?: string[];
  dueAt: string;
  batchKey: string;
  limit: number;
  metadata?: Record<string, unknown>;
}

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

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

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

function 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 boundedNumberValue(value: unknown, fallback: number, min: number, max: number) {
  const parsed = numberValue(value, fallback);
  return Math.max(min, Math.min(max, parsed));
}

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

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 permissionKeys(permission: string) {
  const parts = permission.split(':').filter(Boolean);
  const keys = [permission];
  for (let i = parts.length - 1; i >= 1; i -= 1) {
    keys.push(`${parts.slice(0, i).join(':')}:*`);
  }
  keys.push('*');
  return keys;
}

function explicitPermission(permissions: Record<string, unknown>, permission: string) {
  for (const key of permissionKeys(permission)) {
    const value = permissions[key];
    if (typeof value === 'boolean') return value;
  }
  return null;
}

function hasSupervisionPermission(auth: SupervisionAuthContext, permission: string) {
  if ('roleTemplateId' in auth) {
    return hasTenantPermission(auth as TenantAdminAuth, permission);
  }
  const explicit = explicitPermission(auth.permissions, permission);
  if (explicit !== null) return explicit;
  const templateExplicit = explicitPermission(auth.templatePermissions, permission);
  if (templateExplicit !== null) return templateExplicit;
  return auth.role === 'tenant_owner' || auth.role === 'tenant_admin' || auth.permissions['*'] === true;
}

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

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

export function parseSupervisionRules(value: unknown): SupervisionRuleConfig {
  const raw = objectValue(value);
  return {
    windowDays: boundedIntValue(raw.windowDays, SUPERVISION_RULE_DEFAULTS.windowDays, 3, 90),
    inactivityDays: boundedIntValue(raw.inactivityDays, SUPERVISION_RULE_DEFAULTS.inactivityDays, 1, 90),
    minAnswers: boundedIntValue(raw.minAnswers, SUPERVISION_RULE_DEFAULTS.minAnswers, 1, 500),
    lowAccuracyThreshold: boundedNumberValue(raw.lowAccuracyThreshold, SUPERVISION_RULE_DEFAULTS.lowAccuracyThreshold, 0.1, 0.95),
    wrongQuestionThreshold: boundedIntValue(raw.wrongQuestionThreshold, SUPERVISION_RULE_DEFAULTS.wrongQuestionThreshold, 1, 200),
    vocabularyDueThreshold: boundedIntValue(raw.vocabularyDueThreshold, SUPERVISION_RULE_DEFAULTS.vocabularyDueThreshold, 1, 1000),
    staleSessionDays: boundedIntValue(raw.staleSessionDays, SUPERVISION_RULE_DEFAULTS.staleSessionDays, 1, 30),
  };
}

export function parseSupervisionSchedule(value: unknown): SupervisionScheduleConfig {
  const raw = objectValue(value);
  const frequency = nullableString(raw.frequency) || 'manual';
  if (!['manual', 'daily', 'weekly'].includes(frequency)) {
    throw new HttpError(400, 'Unsupported supervision schedule frequency', 'INVALID_SUPERVISION_SCHEDULE');
  }
  const weekdays = (Array.isArray(raw.weekdays) ? raw.weekdays : [])
    .map(item => boundedIntValue(item, 1, 1, 7))
    .filter((item, index, array) => array.indexOf(item) === index);
  return {
    enabled: boolValue(raw.enabled, frequency !== 'manual'),
    frequency: frequency as SupervisionScheduleConfig['frequency'],
    hour: boundedIntValue(raw.hour, 9, 0, 23),
    minute: boundedIntValue(raw.minute, 0, 0, 59),
    timezone: 'Asia/Shanghai',
    weekdays: weekdays.length ? weekdays : [1, 2, 3, 4, 5],
  };
}

export function supervisionBatchKey(value: unknown) {
  const candidate = nullableString(value);
  if (!candidate) return `manual:${shanghaiTodayKey()}`;
  return candidate.length <= 120 ? candidate : createHash('sha256').update(candidate).digest('hex');
}

export function defaultSupervisionDueAt() {
  const date = new Date();
  date.setDate(date.getDate() + 1);
  date.setHours(18, 0, 0, 0);
  return date.toISOString();
}

export function shanghaiTodayKey(date = new Date()) {
  return shanghaiDateKey(date);
}

function shanghaiDateKey(date: Date) {
  const shanghai = new Date(date.getTime() + 8 * 60 * 60 * 1000);
  return shanghai.toISOString().slice(0, 10);
}

function shanghaiParts(date: Date) {
  const shanghai = new Date(date.getTime() + 8 * 60 * 60 * 1000);
  const day = shanghai.getUTCDay();
  return {
    year: shanghai.getUTCFullYear(),
    month: shanghai.getUTCMonth() + 1,
    date: shanghai.getUTCDate(),
    weekday: day === 0 ? 7 : day,
  };
}

function shanghaiLocalIso(parts: { year: number; month: number; date: number }, hour: number, minute: number) {
  return new Date(Date.UTC(parts.year, parts.month - 1, parts.date, hour - 8, minute, 0, 0)).toISOString();
}

function addShanghaiDays(parts: { year: number; month: number; date: number }, days: number) {
  const shifted = new Date(Date.UTC(parts.year, parts.month - 1, parts.date + days, 0, 0, 0, 0));
  return {
    year: shifted.getUTCFullYear(),
    month: shifted.getUTCMonth() + 1,
    date: shifted.getUTCDate(),
    weekday: shifted.getUTCDay() === 0 ? 7 : shifted.getUTCDay(),
  };
}

export function nextSupervisionRunAt(schedule: SupervisionScheduleConfig, now = new Date()) {
  if (!schedule.enabled || schedule.frequency === 'manual') return null;
  const today = shanghaiParts(now);
  if (schedule.frequency === 'daily') {
    const todayIso = shanghaiLocalIso(today, schedule.hour, schedule.minute);
    if (Date.parse(todayIso) > now.getTime()) return todayIso;
    return shanghaiLocalIso(addShanghaiDays(today, 1), schedule.hour, schedule.minute);
  }

  for (let offset = 0; offset <= 14; offset += 1) {
    const day = addShanghaiDays(today, offset);
    if (!schedule.weekdays.includes(day.weekday)) continue;
    const candidate = shanghaiLocalIso(day, schedule.hour, schedule.minute);
    if (Date.parse(candidate) > now.getTime()) return candidate;
  }
  return shanghaiLocalIso(addShanghaiDays(today, 7), schedule.hour, schedule.minute);
}

function supervisionPriority(score: number) {
  if (score >= 90) return 'urgent';
  if (score >= 60) return 'high';
  return 'normal';
}

export async function scopedSupervisionClassIds(auth: SupervisionAuthContext) {
  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::uuid
      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)]));
}

export async function ensureReadableSupervisionClass(auth: SupervisionAuthContext, classId: string) {
  const allowedClassIds = await scopedSupervisionClassIds(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::uuid limit 1',
    [auth.tenantId, classId],
  );
  if (!rows[0]) throw new HttpError(404, 'Class not found', 'CLASS_NOT_FOUND');
}

export async function ensureStudentInSupervisionScope(auth: SupervisionAuthContext, studentUserId: string) {
  const scopedIds = await scopedSupervisionClassIds(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');
  }
}

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

export async function ensureSupervisionTenantClass(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::uuid limit 1',
    [tenantId, classId],
  );
  if (!result.rows[0]) throw new HttpError(404, 'Class not found', 'CLASS_NOT_FOUND');
}

export async function loadStudentSupervisionCandidates(auth: SupervisionAuthContext, input: {
  rules: SupervisionRuleConfig;
  classId: string | null;
  assignedToUserId: string | null;
  studentUserIds?: string[];
  limit: number;
}) {
  const scopedIds = await scopedSupervisionClassIds(auth);
  const params: unknown[] = [auth.tenantId, input.rules.windowDays, input.rules.staleSessionDays];
  const filters = [
    'tm.tenant_id = $1',
    `tm.role = 'student'`,
    `tm.status = 'active'`,
  ];

  if (input.classId) {
    params.push(input.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'
    )`);
  }
  if (input.studentUserIds?.length) {
    params.push(input.studentUserIds);
    filters.push(`tm.user_id = any($${params.length}::uuid[])`);
  }

  params.push(Math.max(1, Math.min(MAX_SUPERVISION_GENERATE, input.limit)));
  const rows = await query<Record<string, unknown>>(
    `
    with student_scope as (
      select tm.tenant_id, tm.user_id, tm.created_at
      from public.tenant_memberships tm
      where ${filters.join(' and ')}
    ),
    class_agg as (
      select tcm.tenant_id, tcm.user_id,
             min(tcm.class_id::text) as "defaultClassId",
             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
      join student_scope ss on ss.tenant_id = tcm.tenant_id and ss.user_id = tcm.user_id
      where tcm.member_type = 'student'
        and tcm.status = 'active'
      group by tcm.tenant_id, tcm.user_id
    ),
    answers as (
      select ar.tenant_id, ar.user_id,
             count(*) filter (where ar.answered_at >= now() - ($2::int * interval '1 day'))::int as "answerCount",
             count(*) filter (where ar.answered_at >= now() - ($2::int * interval '1 day') and ar.is_correct is true)::int as "correctCount",
             count(*) filter (where ar.answered_at >= now() - ($2::int * interval '1 day') and ar.is_correct is false)::int as "wrongAnswerCount",
             max(ar.answered_at) as "latestAnsweredAt"
      from public.answer_records ar
      join student_scope ss on ss.tenant_id = ar.tenant_id and ss.user_id = ar.user_id
      group by ar.tenant_id, ar.user_id
    ),
    wrongs as (
      select wq.tenant_id, wq.user_id,
             count(*) filter (where wq.resolved_at is null)::int as "unresolvedWrongQuestions",
             coalesce(sum(wq.wrong_count) filter (where wq.resolved_at is null), 0)::int as "wrongQuestionAttempts",
             max(wq.last_wrong_at) filter (where wq.resolved_at is null) as "latestWrongAt"
      from public.wrong_questions wq
      join student_scope ss on ss.tenant_id = wq.tenant_id and ss.user_id = wq.user_id
      group by wq.tenant_id, wq.user_id
    ),
    sessions as (
      select ps.tenant_id, ps.user_id,
             count(*) filter (
               where ps.finished_at is null
                 and ps.started_at <= now() - ($3::int * interval '1 day')
                 and (ps.expires_at is null or ps.expires_at > now())
             )::int as "staleActiveSessions",
             max(ps.started_at) as "latestSessionAt"
      from public.practice_sessions ps
      join student_scope ss on ss.tenant_id = ps.tenant_id and ss.user_id = ps.user_id
      group by ps.tenant_id, ps.user_id
    ),
    reports as (
      select pr.tenant_id, pr.user_id,
             max(pr.submitted_at) as "latestReportAt",
             min(pr.accuracy) filter (where pr.submitted_at >= now() - ($2::int * interval '1 day')) as "lowestReportAccuracy",
             max(pr.score) filter (where pr.submitted_at >= now() - ($2::int * interval '1 day')) as "bestReportScore"
      from public.practice_session_reports pr
      join student_scope ss on ss.tenant_id = pr.tenant_id and ss.user_id = pr.user_id
      group by pr.tenant_id, pr.user_id
    ),
    vocab as (
      select uwp.tenant_id, uwp.user_id,
             count(*) filter (
               where uwp.status in ('new', 'learning', 'reviewing')
                 and (uwp.next_review_date is null or uwp.next_review_date <= now())
             )::int as "dueVocabularyWords",
             count(*) filter (where uwp.status = 'mastered')::int as "masteredVocabularyWords",
             max(uwp.last_review_date) as "latestVocabularyReviewAt"
      from public.user_word_progress uwp
      join student_scope ss on ss.tenant_id = uwp.tenant_id and ss.user_id = uwp.user_id
      group by uwp.tenant_id, uwp.user_id
    )
    select ss.user_id as "studentUserId",
           u.username, u.name, u.email::text as email,
           case when ${canSeeStudentPhone(auth) ? 'true' : 'false'} then u.phone else null end as "studentPhone",
           sp.region_id as "regionId", r.name as "regionName",
           sp.selected_school_id as "selectedSchoolId", school.name as "selectedSchoolName",
           sp.selected_major_id as "selectedMajorId", major.name as "selectedMajorName",
           coalesce(sp.mastered_words_count, 0)::int as "profileMasteredWords",
           coalesce(ca."defaultClassId", null) as "defaultClassId",
           coalesce(ca.classes, '[]'::jsonb) as classes,
           coalesce(a."answerCount", 0)::int as "answerCount",
           coalesce(a."correctCount", 0)::int as "correctCount",
           coalesce(a."wrongAnswerCount", 0)::int as "wrongAnswerCount",
           a."latestAnsweredAt",
           coalesce(w."unresolvedWrongQuestions", 0)::int as "unresolvedWrongQuestions",
           coalesce(w."wrongQuestionAttempts", 0)::int as "wrongQuestionAttempts",
           w."latestWrongAt",
           coalesce(s."staleActiveSessions", 0)::int as "staleActiveSessions",
           s."latestSessionAt",
           rep."latestReportAt",
           rep."lowestReportAccuracy",
           rep."bestReportScore",
           coalesce(v."dueVocabularyWords", 0)::int as "dueVocabularyWords",
           coalesce(v."masteredVocabularyWords", 0)::int as "masteredVocabularyWords",
           v."latestVocabularyReviewAt"
    from student_scope ss
    join public.platform_users u on u.id = ss.user_id
    left join public.student_profiles sp on sp.tenant_id = ss.tenant_id and sp.user_id = ss.user_id
    left join public.regions r on r.tenant_id = ss.tenant_id and r.id = sp.region_id
    left join public.schools school on school.tenant_id = ss.tenant_id and school.id = sp.selected_school_id
    left join public.majors major on major.tenant_id = ss.tenant_id and major.id = sp.selected_major_id
    left join class_agg ca on ca.tenant_id = ss.tenant_id and ca.user_id = ss.user_id
    left join answers a on a.tenant_id = ss.tenant_id and a.user_id = ss.user_id
    left join wrongs w on w.tenant_id = ss.tenant_id and w.user_id = ss.user_id
    left join sessions s on s.tenant_id = ss.tenant_id and s.user_id = ss.user_id
    left join reports rep on rep.tenant_id = ss.tenant_id and rep.user_id = ss.user_id
    left join vocab v on v.tenant_id = ss.tenant_id and v.user_id = ss.user_id
    order by coalesce(a."latestAnsweredAt", s."latestSessionAt", v."latestVocabularyReviewAt", ss.created_at) asc nulls first
    limit $${params.length}
    `,
    params,
  );

  return rows.map(row => buildSupervisionCandidate(row, input.rules, {
    assignedToUserId: input.assignedToUserId,
    classId: input.classId,
  })).filter(Boolean) as Record<string, unknown>[];
}

function buildSupervisionCandidate(
  row: Record<string, unknown>,
  rules: SupervisionRuleConfig,
  options: { assignedToUserId: string | null; classId: string | null },
) {
  const answerCount = intValue(row.answerCount, 0);
  const correctCount = intValue(row.correctCount, 0);
  const unresolvedWrongQuestions = intValue(row.unresolvedWrongQuestions, 0);
  const staleActiveSessions = intValue(row.staleActiveSessions, 0);
  const dueVocabularyWords = intValue(row.dueVocabularyWords, 0);
  const latestStudyAt = latestIso([
    row.latestAnsweredAt,
    row.latestSessionAt,
    row.latestVocabularyReviewAt,
    row.latestReportAt,
  ]);
  const inactiveDays = latestStudyAt ? Math.floor((Date.now() - new Date(latestStudyAt).getTime()) / 86_400_000) : null;
  const accuracy = answerCount > 0 ? correctCount / answerCount : null;
  const reasons: Array<Record<string, unknown>> = [];
  let riskScore = 0;

  if (inactiveDays === null || inactiveDays >= rules.inactivityDays) {
    const score = inactiveDays === null ? 35 : Math.min(55, inactiveDays * 5);
    riskScore += score;
    reasons.push({
      code: 'inactive',
      label: inactiveDays === null ? '未产生学习记录' : `${inactiveDays} 天未学习`,
      severity: inactiveDays === null || inactiveDays >= rules.inactivityDays * 2 ? 'high' : 'medium',
      value: inactiveDays,
      threshold: rules.inactivityDays,
    });
  }

  if (unresolvedWrongQuestions >= rules.wrongQuestionThreshold) {
    riskScore += Math.min(45, unresolvedWrongQuestions * 4);
    reasons.push({
      code: 'wrong_backlog',
      label: `未解决错题 ${unresolvedWrongQuestions} 道`,
      severity: unresolvedWrongQuestions >= rules.wrongQuestionThreshold * 2 ? 'high' : 'medium',
      value: unresolvedWrongQuestions,
      threshold: rules.wrongQuestionThreshold,
    });
  }

  if (answerCount >= rules.minAnswers && accuracy !== null && accuracy < rules.lowAccuracyThreshold) {
    riskScore += Math.round((rules.lowAccuracyThreshold - accuracy) * 100);
    reasons.push({
      code: 'low_accuracy',
      label: `近 ${rules.windowDays} 天正确率 ${Math.round(accuracy * 100)}%`,
      severity: accuracy < rules.lowAccuracyThreshold * 0.75 ? 'high' : 'medium',
      value: Number(accuracy.toFixed(4)),
      threshold: rules.lowAccuracyThreshold,
    });
  }

  if (dueVocabularyWords >= rules.vocabularyDueThreshold) {
    riskScore += Math.min(35, Math.ceil(dueVocabularyWords / Math.max(1, rules.vocabularyDueThreshold)) * 10);
    reasons.push({
      code: 'vocabulary_due',
      label: `待复习单词 ${dueVocabularyWords} 个`,
      severity: dueVocabularyWords >= rules.vocabularyDueThreshold * 2 ? 'high' : 'medium',
      value: dueVocabularyWords,
      threshold: rules.vocabularyDueThreshold,
    });
  }

  if (staleActiveSessions > 0) {
    riskScore += Math.min(25, staleActiveSessions * 10);
    reasons.push({
      code: 'stale_session',
      label: `存在 ${staleActiveSessions} 个未完成练习`,
      severity: 'medium',
      value: staleActiveSessions,
      threshold: 1,
    });
  }

  if (!reasons.length) return null;
  const priority = supervisionPriority(riskScore);
  const studentName = nullableString(row.name) || nullableString(row.username) || nullableString(row.studentPhone) || String(row.studentUserId);
  const primaryReason = String(reasons[0]?.label || '学习状态需要跟进');
  const classId = options.classId || nullableString(row.defaultClassId);
  return {
    studentUserId: row.studentUserId,
    studentName,
    studentPhone: row.studentPhone,
    assignedToUserId: options.assignedToUserId,
    classId,
    classes: row.classes,
    title: `学习督导：${primaryReason}`,
    description: reasons.map(item => item.label).join('；'),
    followupType: 'learning',
    priority,
    riskScore: Math.min(100, Math.round(riskScore)),
    reasons,
    evidence: {
      windowDays: rules.windowDays,
      answerCount,
      correctCount,
      wrongAnswerCount: intValue(row.wrongAnswerCount, 0),
      accuracy: accuracy === null ? null : Number(accuracy.toFixed(4)),
      latestStudyAt,
      inactiveDays,
      unresolvedWrongQuestions,
      wrongQuestionAttempts: intValue(row.wrongQuestionAttempts, 0),
      staleActiveSessions,
      dueVocabularyWords,
      masteredVocabularyWords: intValue(row.masteredVocabularyWords, 0),
      latestWrongAt: row.latestWrongAt || null,
      latestReportAt: row.latestReportAt || null,
      lowestReportAccuracy: row.lowestReportAccuracy === null || row.lowestReportAccuracy === undefined ? null : numberValue(row.lowestReportAccuracy, 0),
      bestReportScore: row.bestReportScore === null || row.bestReportScore === undefined ? null : numberValue(row.bestReportScore, 0),
      latestVocabularyReviewAt: row.latestVocabularyReviewAt || null,
    },
  };
}

function latestIso(values: unknown[]) {
  const timestamps = values
    .map(value => (typeof value === 'string' || value instanceof Date ? new Date(value).getTime() : NaN))
    .filter(Number.isFinite);
  if (!timestamps.length) return null;
  return new Date(Math.max(...timestamps)).toISOString();
}

export async function generateStudentSupervisionFollowups(
  auth: SupervisionAuthContext,
  input: StudentSupervisionGenerateInput,
) {
  const limit = Math.max(1, Math.min(MAX_SUPERVISION_GENERATE, input.limit));
  return transaction(async client => {
    await ensureSupervisionAssignee(client, auth.tenantId, input.assignedToUserId, 'ASSIGNEE_NOT_FOUND');
    let candidates = await loadStudentSupervisionCandidates(auth, {
      rules: input.rules,
      classId: input.classId,
      assignedToUserId: input.assignedToUserId,
      studentUserIds: input.studentUserIds,
      limit: Math.max(limit, input.studentUserIds?.length || 0) || limit,
    });
    candidates = candidates.slice(0, limit);

    const items: unknown[] = [];
    const errors: unknown[] = [];
    for (let index = 0; index < candidates.length; index += 1) {
      const candidate = candidates[index];
      const studentUserId = String(candidate.studentUserId || '');
      try {
        await ensureStudentInSupervisionScope(auth, studentUserId);
        const candidateClassId = nullableString(candidate.classId);
        if (candidateClassId) await ensureSupervisionTenantClass(client, auth.tenantId, candidateClassId);
        const idempotencyKey = `student_supervision:${input.batchKey}:${studentUserId}`;
        const inputMetadata = objectValue(input.metadata);
        const metadata = {
          ...inputMetadata,
          autoSupervision: {
            idempotencyKey,
            batchKey: input.batchKey,
            source: inputMetadata.source || 'manual',
            ruleId: inputMetadata.ruleId || null,
            ruleName: inputMetadata.ruleName || null,
            workerId: inputMetadata.workerId || null,
            generatedAt: new Date().toISOString(),
            rules: input.rules,
            riskScore: candidate.riskScore,
            reasons: candidate.reasons,
            evidence: candidate.evidence,
          },
        };
        const followup = await client.query<Record<string, unknown>>(
          `
          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, 'learning', $7, 'open', $8::timestamptz,
            $9::jsonb, $10::uuid, $10::uuid
          )
          on conflict (tenant_id, (metadata->'autoSupervision'->>'idempotencyKey'))
          where metadata->'autoSupervision'->>'idempotencyKey' is not null
          do update set assigned_to_user_id = coalesce(excluded.assigned_to_user_id, public.tenant_student_followups.assigned_to_user_id),
                        class_id = coalesce(excluded.class_id, public.tenant_student_followups.class_id),
                        title = excluded.title,
                        description = excluded.description,
                        priority = excluded.priority,
                        due_at = excluded.due_at,
                        metadata = public.tenant_student_followups.metadata || excluded.metadata,
                        updated_by = excluded.updated_by,
                        updated_at = now()
          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", metadata, created_at as "createdAt", updated_at as "updatedAt"
          `,
          [
            auth.tenantId,
            studentUserId,
            input.assignedToUserId,
            candidateClassId,
            candidate.title,
            candidate.description,
            candidate.priority,
            input.dueAt,
            JSON.stringify(metadata),
            auth.userId,
          ],
        );
        items.push({ index, studentUserId, followupId: followup.rows[0].id, item: followup.rows[0] });
      } catch (error) {
        errors.push({
          index,
          studentUserId,
          code: error instanceof HttpError ? error.code : 'STUDENT_SUPERVISION_ITEM_FAILED',
          message: error instanceof Error ? error.message : 'Student supervision item failed',
        });
      }
    }

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2::uuid, 'tenant.students.supervision_generated', 'tenant_student_followups', null, $3::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        JSON.stringify({
          batchKey: input.batchKey,
          classId: input.classId,
          assignedToUserId: input.assignedToUserId,
          total: candidates.length,
          successCount: items.length,
          errorCount: errors.length,
          rules: input.rules,
          source: objectValue(input.metadata).source || 'manual',
          ruleId: objectValue(input.metadata).ruleId || null,
        }),
      ],
    );

    return {
      batchKey: input.batchKey,
      total: candidates.length,
      successCount: items.length,
      errorCount: errors.length,
      items,
      errors,
    };
  });
}

export function supervisionSystemAuth(tenantId: string): SupervisionAuthContext {
  return {
    tenantId,
    userId: null,
    role: 'tenant_admin',
    permissions: { '*': true },
    templatePermissions: {},
    fieldPermissions: { 'student.phone': false },
    dataScope: {},
  };
}
