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

type JsonObject = Record<string, unknown>;

interface StudentProfileContext {
  regionId: string | null;
  regionName: string | null;
  selectedSchoolId: string | null;
  selectedSchoolName: string | null;
  selectedMajorId: string | null;
  selectedMajorName: string | null;
  stats: JsonObject;
}

interface RegionRow {
  id: string;
  name: string;
  code: string | null;
}

interface EntitlementRow {
  id: string;
  scopeType: string;
  scopeId: string | null;
  expiresAt: string | null;
}

interface ScorelineFieldRow {
  fieldKey: string;
  fieldName: string;
  fieldType: string | null;
  unit: string | null;
  isTrend: boolean;
  sortOrder: number;
}

interface ScorelineRecordRow {
  id: string;
  year: number;
  schoolId: string | null;
  schoolName: string | null;
  majorId: string | null;
  majorName: string | null;
  fieldValues: JsonObject;
}

interface RecommendationCandidate {
  schoolId: string | null;
  schoolName: string;
  majorId: string | null;
  majorName: string | null;
  latestYear: number | null;
  latestScore: number | null;
  averageScore: number | null;
  scoreGap: number | null;
  riskLevel: 'safe' | 'balanced' | 'sprint' | 'unknown';
  confidence: number;
  reason: string;
  scorelineTrend: {
    years: number[];
    scores: (number | null)[];
    direction: 'up' | 'down' | 'flat' | 'unknown';
  };
  tags: string[];
}

interface SchoolRecommendationReportRow {
  id: string;
  tenantId: string;
  userId: string;
  regionId: string | null;
  status: string;
  provider: string;
  model: string | null;
  promptVersion: string;
  inputPayload: JsonObject;
  contextPayload: JsonObject;
  resultPayload: JsonObject;
  errorMessage: string | null;
  generatedAt: string | null;
  createdAt: string;
  updatedAt: string;
}

const RISK_PREFERENCES = new Set(['safe', 'balanced', 'sprint']);
const PROMPT_VERSION = 'school-recommendation-v1';
const LOCAL_MODEL = 'local-scoreline-rules-v1';
const DISCLAIMER = [
  '推荐结果仅用于择校和备考规划参考，不构成录取承诺。',
  '分数线、招生计划和考试政策可能变化，正式报考前应以院校和考试院官方信息为准。',
  '当地区或院校数据覆盖不足时，应结合人工咨询和最新招生简章复核。',
];

function objectValue(value: unknown): JsonObject {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as JsonObject : {};
}

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

function boundedNumber(value: unknown, min: number, max: number) {
  const parsed = Number(value);
  if (!Number.isFinite(parsed)) return null;
  return Math.min(Math.max(parsed, min), max);
}

function boundedText(value: unknown, maxLength: number) {
  const text = nullableString(value);
  if (!text) return null;
  return text.slice(0, maxLength);
}

function textValue(value: unknown, fallback = '') {
  if (value === null || value === undefined) return fallback;
  if (typeof value === 'object') return JSON.stringify(value);
  return String(value);
}

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

function arrayValue(value: unknown) {
  return Array.isArray(value) ? value : [];
}

function htmlEscape(value: unknown) {
  return textValue(value)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#39;');
}

function markdownText(value: unknown) {
  return textValue(value).replace(/[<>]/g, '');
}

function contentBase64AndHash(content: string) {
  const buffer = Buffer.from(content, 'utf8');
  return {
    contentBase64: buffer.toString('base64'),
    sha256: crypto.createHash('sha256').update(buffer).digest('hex'),
    sizeBytes: buffer.length,
  };
}

function reportExportFormat(value: string) {
  const format = value || 'markdown';
  if (!['markdown', 'html'].includes(format)) {
    throw new HttpError(400, 'format must be markdown or html', 'AI_REPORT_EXPORT_FORMAT_INVALID');
  }
  return format as 'markdown' | 'html';
}

function normalizeRiskPreference(value: unknown) {
  const riskPreference = nullableString(value) || 'balanced';
  if (!RISK_PREFERENCES.has(riskPreference)) {
    throw new HttpError(400, 'riskPreference must be safe, balanced, or sprint', 'INVALID_AI_RISK_PREFERENCE');
  }
  return riskPreference as 'safe' | 'balanced' | 'sprint';
}

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

function pickScoreValue(values: JsonObject) {
  const preferredKeys = [
    'minScore',
    'minimumScore',
    'score',
    'admissionScore',
    'lowestScore',
    '投档线',
    '最低分',
  ];
  for (const key of preferredKeys) {
    const score = numericField(values[key]);
    if (score !== null) return score;
  }
  for (const [key, value] of Object.entries(values)) {
    if (/score|分|线/i.test(key)) {
      const score = numericField(value);
      if (score !== null) return score;
    }
  }
  return null;
}

function riskFromGap(gap: number | null, preference: 'safe' | 'balanced' | 'sprint') {
  if (gap === null) return 'unknown';
  const safeFloor = preference === 'safe' ? 18 : preference === 'sprint' ? 8 : 12;
  const sprintFloor = preference === 'safe' ? -2 : preference === 'sprint' ? -15 : -8;
  if (gap >= safeFloor) return 'safe';
  if (gap >= sprintFloor) return 'balanced';
  return 'sprint';
}

function confidenceFromGap(gap: number | null, recordsCount: number, riskLevel: string) {
  if (gap === null) return recordsCount > 1 ? 0.45 : 0.35;
  const base = riskLevel === 'safe' ? 0.78 : riskLevel === 'balanced' ? 0.62 : 0.42;
  const gapBonus = Math.min(Math.abs(gap) / 100, 0.12);
  const coverageBonus = Math.min(recordsCount * 0.025, 0.1);
  return Number(Math.min(base + gapBonus + coverageBonus, 0.95).toFixed(2));
}

function trendDirection(scores: (number | null)[]) {
  const numericScores = scores.filter((score): score is number => typeof score === 'number');
  if (numericScores.length < 2) return 'unknown';
  const first = numericScores[0];
  const last = numericScores[numericScores.length - 1];
  const diff = last - first;
  if (Math.abs(diff) <= 3) return 'flat';
  return diff > 0 ? 'up' : 'down';
}

function groupKey(record: ScorelineRecordRow) {
  return `${record.schoolId || record.schoolName || 'unknown'}:${record.majorId || record.majorName || 'unknown'}`;
}

function buildCandidates(
  records: ScorelineRecordRow[],
  input: ReturnType<typeof normalizeRecommendationInput>,
) {
  const groups = new Map<string, ScorelineRecordRow[]>();
  for (const record of records) {
    const key = groupKey(record);
    groups.set(key, [...(groups.get(key) || []), record]);
  }

  const candidates = [...groups.values()].map(group => {
    const sorted = group.slice().sort((left, right) => left.year - right.year);
    const latest = sorted.at(-1) || null;
    const scores = sorted.map(record => pickScoreValue(record.fieldValues));
    const numericScores = scores.filter((score): score is number => typeof score === 'number');
    const latestScore = latest ? pickScoreValue(latest.fieldValues) : null;
    const averageScore = numericScores.length
      ? Number((numericScores.reduce((sum, score) => sum + score, 0) / numericScores.length).toFixed(1))
      : null;
    const baseline = latestScore ?? averageScore;
    const scoreGap = baseline === null || input.estimatedScore === null
      ? null
      : Number((input.estimatedScore - baseline).toFixed(1));
    const riskLevel = riskFromGap(scoreGap, input.riskPreference);
    const direction = trendDirection(scores);
    const confidence = confidenceFromGap(scoreGap, sorted.length, riskLevel);
    const tags = [
      riskLevel === 'safe' ? '稳妥' : riskLevel === 'balanced' ? '匹配' : riskLevel === 'sprint' ? '冲刺' : '数据不足',
      direction === 'up' ? '分数线上升' : direction === 'down' ? '分数线下降' : direction === 'flat' ? '分数线稳定' : '趋势不足',
      sorted.length >= 3 ? '多年数据' : '样本较少',
    ];
    const schoolName = latest?.schoolName || group[0]?.schoolName || '未知院校';
    const majorName = latest?.majorName || group[0]?.majorName || null;
    const reasonParts = [
      input.estimatedScore === null
        ? '未提供预估分，按历年分数线和数据覆盖度排序。'
        : `预估分与最新参考线差值约 ${scoreGap ?? '未知'} 分。`,
      direction === 'up'
        ? '近年参考线有上升趋势，建议预留安全分差。'
        : direction === 'down'
          ? '近年参考线略有下降，可作为匹配或冲刺备选。'
          : direction === 'flat'
            ? '近年参考线相对稳定，适合纳入重点比较。'
            : '历史数据不足，建议结合招生计划复核。',
    ];

    return {
      schoolId: latest?.schoolId || group[0]?.schoolId || null,
      schoolName,
      majorId: latest?.majorId || group[0]?.majorId || null,
      majorName,
      latestYear: latest?.year || null,
      latestScore,
      averageScore,
      scoreGap,
      riskLevel,
      confidence,
      reason: reasonParts.join(' '),
      scorelineTrend: {
        years: sorted.map(record => record.year),
        scores,
        direction,
      },
      tags,
    } satisfies RecommendationCandidate;
  });

  const riskOrder = {
    safe: input.riskPreference === 'safe' ? 0 : 1,
    balanced: input.riskPreference === 'balanced' ? 0 : 2,
    sprint: input.riskPreference === 'sprint' ? 0 : 3,
    unknown: 4,
  };

  return candidates
    .sort((left, right) => {
      const riskDiff = riskOrder[left.riskLevel] - riskOrder[right.riskLevel];
      if (riskDiff !== 0) return riskDiff;
      if (right.confidence !== left.confidence) return right.confidence - left.confidence;
      return (right.latestYear || 0) - (left.latestYear || 0);
    })
    .slice(0, input.recommendationLimit);
}

function normalizeRecommendationInput(body: JsonObject) {
  const estimatedScore = boundedNumber(body.estimatedScore, 0, 1000);
  const recommendationLimit = Math.trunc(boundedNumber(body.recommendationLimit, 1, 12) || 5);
  return {
    regionId: nullableString(body.regionId),
    estimatedScore,
    examTrack: boundedText(body.examTrack, 80),
    preferredCity: boundedText(body.preferredCity, 80),
    targetSchoolId: nullableString(body.targetSchoolId),
    targetMajorId: nullableString(body.targetMajorId),
    riskPreference: normalizeRiskPreference(body.riskPreference),
    constraints: boundedText(body.constraints, 500),
    notes: boundedText(body.notes, 500),
    recommendationLimit,
  };
}

async function loadStudentProfile(tenantId: string, userId: string) {
  return queryOne<StudentProfileContext>(
    `
    select 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.stats
    from public.student_profiles sp
    left join public.regions r on r.id = sp.region_id and r.tenant_id = sp.tenant_id
    left join public.schools s on s.id = sp.selected_school_id and s.tenant_id = sp.tenant_id
    left join public.majors m on m.id = sp.selected_major_id and m.tenant_id = sp.tenant_id
    where sp.tenant_id = $1 and sp.user_id = $2
    limit 1
    `,
    [tenantId, userId],
  );
}

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

async function activeSvipEntitlement(tenantId: string, userId: string, regionId: string | null) {
  const now = new Date().toISOString();
  return queryOne<EntitlementRow>(
    `
    select id, scope_type as "scopeType", scope_id as "scopeId", expires_at as "expiresAt"
    from public.entitlements
    where tenant_id = $1
      and user_id = $2
      and entitlement_type = 'svip'
      and status = 'active'
      and starts_at <= $3::timestamptz
      and (expires_at is null or expires_at > $3::timestamptz)
      and (
        scope_type = 'tenant'
        or ($4::uuid is not null and scope_type = 'region' and scope_id = $4::uuid)
      )
    order by case when scope_type = 'region' then 0 else 1 end, expires_at desc nulls first
    limit 1
    `,
    [tenantId, userId, now, regionId],
  );
}

async function loadScorelineFields(tenantId: string, regionId: string) {
  return query<ScorelineFieldRow>(
    `
    select field_key as "fieldKey", field_name as "fieldName",
           field_type as "fieldType", unit, is_trend as "isTrend",
           sort_order as "sortOrder"
    from public.scoreline_fields
    where tenant_id = $1
      and (region_id is null or region_id = $2::uuid)
      and is_visible = true
    order by sort_order asc, field_name asc
    `,
    [tenantId, regionId],
  );
}

async function loadScorelineRecords(tenantId: string, regionId: string, input: ReturnType<typeof normalizeRecommendationInput>) {
  const params: unknown[] = [tenantId, regionId, input.targetSchoolId, input.targetMajorId, 180];
  return query<ScorelineRecordRow>(
    `
    select id, year, school_id as "schoolId", school_name as "schoolName",
           major_id as "majorId", major_name as "majorName",
           field_values as "fieldValues"
    from public.scoreline_records
    where tenant_id = $1
      and region_id = $2::uuid
      and ($3::uuid is null or school_id = $3::uuid)
      and ($4::uuid is null or major_id = $4::uuid)
    order by year desc, school_name asc nulls last, major_name asc nulls last
    limit $5
    `,
    params,
  );
}

function buildReportResult(input: ReturnType<typeof normalizeRecommendationInput>, context: JsonObject, candidates: RecommendationCandidate[]) {
  const region = objectValue(context.region);
  const dataCoverage = objectValue(context.dataCoverage);
  const safeCount = candidates.filter(candidate => candidate.riskLevel === 'safe').length;
  const balancedCount = candidates.filter(candidate => candidate.riskLevel === 'balanced').length;
  const sprintCount = candidates.filter(candidate => candidate.riskLevel === 'sprint').length;
  const top = candidates[0] || null;
  const riskLevel =
    safeCount >= 2 ? 'safe' :
      balancedCount >= 2 || top?.riskLevel === 'balanced' ? 'balanced' :
        sprintCount ? 'sprint' : 'unknown';

  return {
    schemaVersion: 'school-recommendation-report-v1',
    summary: top
      ? `基于当前地区历年分数线，优先推荐 ${top.schoolName}${top.majorName ? `-${top.majorName}` : ''} 等 ${candidates.length} 个方案。`
      : '当前地区分数线数据不足，暂无法生成可靠院校推荐。',
    riskLevel,
    recommendedSchools: candidates,
    actionPlan: [
      '先确认目标地区、考试类别和预估分是否准确。',
      '重点比较推荐院校近三年分数线、招生计划和专业限制。',
      '把稳妥、匹配、冲刺院校分别保留 2-3 个备选，并跟进最新招生简章。',
      input.constraints ? '结合个人限制条件逐项排除不符合报考条件的院校或专业。' : '补充个人限制条件后可再次生成更精细的推荐。',
    ],
    disclaimers: DISCLAIMER,
    dataCoverage: {
      regionId: region.id || null,
      regionName: region.name || null,
      scorelineRecordCount: dataCoverage.scorelineRecordCount || 0,
      schoolMajorGroupCount: candidates.length,
      years: dataCoverage.years || [],
      fields: dataCoverage.fields || [],
      provider: 'local_rules',
    },
  };
}

function reportSelectSql() {
  return `
    select id, tenant_id as "tenantId", user_id as "userId", region_id as "regionId",
           status, provider, model, prompt_version as "promptVersion",
           input_payload as "inputPayload", context_payload as "contextPayload",
           result_payload as "resultPayload", error_message as "errorMessage",
           generated_at as "generatedAt", created_at as "createdAt", updated_at as "updatedAt"
    from public.ai_recommendation_reports
  `;
}

function riskLabel(value: unknown) {
  const labels: Record<string, string> = {
    safe: '稳妥',
    balanced: '均衡',
    sprint: '冲刺',
    unknown: '未知',
  };
  return labels[textValue(value)] || '未知';
}

function directionLabel(value: unknown) {
  const labels: Record<string, string> = {
    up: '上升',
    down: '下降',
    flat: '稳定',
    unknown: '样本不足',
  };
  return labels[textValue(value)] || '样本不足';
}

function reportFileTimestamp(value: string | null | undefined) {
  const source = value ? new Date(value) : new Date();
  const valid = Number.isFinite(source.getTime()) ? source : new Date();
  return valid.toISOString().slice(0, 19).replace(/[-:T]/g, '');
}

function buildReportMarkdown(report: SchoolRecommendationReportRow) {
  const result = objectValue(report.resultPayload);
  const input = objectValue(report.inputPayload);
  const coverage = objectValue(result.dataCoverage);
  const recommendations = arrayValue(result.recommendedSchools).map(objectValue);
  const actionPlan = arrayValue(result.actionPlan);
  const disclaimers = arrayValue(result.disclaimers);

  const lines = [
    '# AI 择校推荐报告',
    '',
    `生成时间：${markdownText(report.generatedAt || report.createdAt)}`,
    `报告编号：${markdownText(report.id)}`,
    `推荐偏好：${riskLabel(input.riskPreference)}`,
    input.estimatedScore !== undefined ? `预估分：${markdownText(input.estimatedScore)}` : '',
    input.constraints ? `限制条件：${markdownText(input.constraints)}` : '',
    '',
    '## 结论摘要',
    markdownText(result.summary || '暂无摘要'),
    '',
    `整体方案：${riskLabel(result.riskLevel)}`,
    '',
    '## 推荐院校',
  ].filter(Boolean);

  recommendations.forEach((candidate, index) => {
    const trend = objectValue(candidate.scorelineTrend);
    const tags = arrayValue(candidate.tags).map(markdownText).filter(Boolean);
    lines.push(
      '',
      `### ${index + 1}. ${markdownText(candidate.schoolName || '未知院校')}${candidate.majorName ? ` · ${markdownText(candidate.majorName)}` : ''}`,
      `风险档位：${riskLabel(candidate.riskLevel)} · 置信度：${Math.round((numberValue(candidate.confidence) || 0) * 100)}%`,
      `最新年份：${markdownText(candidate.latestYear ?? '未知')} · 最新参考线：${markdownText(candidate.latestScore ?? '未知')} · 差值：${markdownText(candidate.scoreGap ?? '未知')}`,
      `均值参考：${markdownText(candidate.averageScore ?? '未知')} · 趋势：${directionLabel(trend.direction)}`,
      `理由：${markdownText(candidate.reason || '暂无理由')}`,
      tags.length ? `标签：${tags.join('、')}` : '',
    );
  });

  if (!recommendations.length) {
    lines.push('', '暂无可推荐院校。');
  }

  lines.push('', '## 行动计划');
  actionPlan.forEach((item, index) => {
    lines.push(`${index + 1}. ${markdownText(item)}`);
  });
  if (!actionPlan.length) lines.push('暂无行动计划。');

  lines.push('', '## 数据覆盖');
  lines.push(`地区：${markdownText(coverage.regionName || coverage.regionId || report.regionId || '未知')}`);
  lines.push(`分数线样本：${markdownText(coverage.scorelineRecordCount ?? 0)}`);
  lines.push(`候选组数：${markdownText(coverage.schoolMajorGroupCount ?? recommendations.length)}`);
  const years = arrayValue(coverage.years).map(markdownText).filter(Boolean);
  if (years.length) lines.push(`覆盖年份：${years.join('、')}`);

  lines.push('', '## 免责声明');
  disclaimers.forEach(item => {
    lines.push(`- ${markdownText(item)}`);
  });
  if (!disclaimers.length) {
    DISCLAIMER.forEach(item => lines.push(`- ${markdownText(item)}`));
  }

  return `${lines.filter(line => line !== '').join('\n')}\n`;
}

function buildReportHtml(report: SchoolRecommendationReportRow) {
  const result = objectValue(report.resultPayload);
  const input = objectValue(report.inputPayload);
  const coverage = objectValue(result.dataCoverage);
  const recommendations = arrayValue(result.recommendedSchools).map(objectValue);
  const actionPlan = arrayValue(result.actionPlan);
  const disclaimers = arrayValue(result.disclaimers).length ? arrayValue(result.disclaimers) : DISCLAIMER;
  const rows = recommendations.map((candidate, index) => {
    const trend = objectValue(candidate.scorelineTrend);
    const tags = arrayValue(candidate.tags).map(htmlEscape).filter(Boolean);
    return `
      <section class="candidate">
        <h3>${index + 1}. ${htmlEscape(candidate.schoolName || '未知院校')}${candidate.majorName ? ` · ${htmlEscape(candidate.majorName)}` : ''}</h3>
        <p><strong>${riskLabel(candidate.riskLevel)}</strong> · 置信度 ${Math.round((numberValue(candidate.confidence) || 0) * 100)}%</p>
        <p>最新年份：${htmlEscape(candidate.latestYear ?? '未知')} · 最新参考线：${htmlEscape(candidate.latestScore ?? '未知')} · 差值：${htmlEscape(candidate.scoreGap ?? '未知')}</p>
        <p>均值参考：${htmlEscape(candidate.averageScore ?? '未知')} · 趋势：${directionLabel(trend.direction)}</p>
        <p>${htmlEscape(candidate.reason || '暂无理由')}</p>
        ${tags.length ? `<p class="tags">${tags.join(' / ')}</p>` : ''}
      </section>
    `;
  }).join('\n');
  const years = arrayValue(coverage.years).map(htmlEscape).filter(Boolean);

  return `<!doctype html>
<html lang="zh-CN">
<head>
  <meta charset="utf-8" />
  <title>AI 择校推荐报告</title>
  <style>
    body { margin: 0; padding: 32px; color: #111827; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif; line-height: 1.65; background: #f8fafc; }
    main { max-width: 880px; margin: 0 auto; padding: 32px; border: 1px solid #e2e8f0; border-radius: 8px; background: #fff; }
    h1, h2, h3 { line-height: 1.25; }
    .meta, .coverage, .disclaimer { color: #475569; }
    .summary { padding: 18px; border: 1px solid #bfdbfe; border-radius: 8px; background: #eff6ff; }
    .candidate { margin-top: 18px; padding: 18px; border: 1px solid #e2e8f0; border-radius: 8px; }
    .tags { color: #1d4ed8; font-weight: 700; }
  </style>
</head>
<body>
  <main>
    <h1>AI 择校推荐报告</h1>
    <p class="meta">生成时间：${htmlEscape(report.generatedAt || report.createdAt)} · 报告编号：${htmlEscape(report.id)}</p>
    <p class="meta">推荐偏好：${riskLabel(input.riskPreference)}${input.estimatedScore !== undefined ? ` · 预估分：${htmlEscape(input.estimatedScore)}` : ''}</p>
    ${input.constraints ? `<p class="meta">限制条件：${htmlEscape(input.constraints)}</p>` : ''}
    <section class="summary">
      <h2>结论摘要</h2>
      <p>${htmlEscape(result.summary || '暂无摘要')}</p>
      <p>整体方案：${riskLabel(result.riskLevel)}</p>
    </section>
    <h2>推荐院校</h2>
    ${rows || '<p>暂无可推荐院校。</p>'}
    <h2>行动计划</h2>
    <ol>${actionPlan.map(item => `<li>${htmlEscape(item)}</li>`).join('') || '<li>暂无行动计划。</li>'}</ol>
    <h2>数据覆盖</h2>
    <p class="coverage">地区：${htmlEscape(coverage.regionName || coverage.regionId || report.regionId || '未知')} · 分数线样本：${htmlEscape(coverage.scorelineRecordCount ?? 0)} · 候选组数：${htmlEscape(coverage.schoolMajorGroupCount ?? recommendations.length)}${years.length ? ` · 覆盖年份：${years.join('、')}` : ''}</p>
    <h2>免责声明</h2>
    <ul class="disclaimer">${disclaimers.map(item => `<li>${htmlEscape(item)}</li>`).join('')}</ul>
  </main>
</body>
</html>
`;
}

function buildReportExport(report: SchoolRecommendationReportRow, format: 'markdown' | 'html') {
  const content = format === 'html' ? buildReportHtml(report) : buildReportMarkdown(report);
  const encoded = contentBase64AndHash(content);
  const extension = format === 'html' ? 'html' : 'md';
  return {
    reportId: report.id,
    format,
    fileName: `school-recommendation-${reportFileTimestamp(report.generatedAt || report.createdAt)}-${report.id.slice(0, 8)}.${extension}`,
    mimeType: format === 'html' ? 'text/html; charset=utf-8' : 'text/markdown; charset=utf-8',
    contentText: content,
    ...encoded,
  };
}

export async function generateSchoolRecommendationRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const input = normalizeRecommendationInput(body);
  const profile = await loadStudentProfile(tenantId, userId);
  if (!profile) throw new HttpError(404, 'Student profile not found', 'PROFILE_NOT_FOUND');

  const regionId = input.regionId || profile.regionId;
  if (!regionId) throw new HttpError(400, 'regionId is required before generating a recommendation', 'AI_REGION_REQUIRED');
  const region = await assertRegionAccess(tenantId, regionId);
  const entitlement = await activeSvipEntitlement(tenantId, userId, regionId);
  if (!entitlement) {
    throw new HttpError(403, 'SVIP entitlement is required for AI school recommendation', 'AI_SVIP_REQUIRED');
  }

  const [fields, records] = await Promise.all([
    loadScorelineFields(tenantId, regionId),
    loadScorelineRecords(tenantId, regionId, input),
  ]);
  const years = [...new Set(records.map(record => record.year))].sort((left, right) => right - left);
  const candidates = buildCandidates(records, input);
  const contextPayload = {
    region,
    studentProfile: {
      regionId: profile.regionId,
      regionName: profile.regionName,
      selectedSchoolId: profile.selectedSchoolId,
      selectedSchoolName: profile.selectedSchoolName,
      selectedMajorId: profile.selectedMajorId,
      selectedMajorName: profile.selectedMajorName,
    },
    entitlement: {
      id: entitlement.id,
      scopeType: entitlement.scopeType,
      scopeId: entitlement.scopeId,
      expiresAt: entitlement.expiresAt,
    },
    dataCoverage: {
      scorelineRecordCount: records.length,
      years,
      fields: fields.map(field => ({
        fieldKey: field.fieldKey,
        fieldName: field.fieldName,
        fieldType: field.fieldType,
        unit: field.unit,
        isTrend: field.isTrend,
      })),
    },
  };
  const resultPayload = buildReportResult(input, contextPayload, candidates);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.ai_recommendation_reports (
        tenant_id, user_id, region_id, status, provider, model, prompt_version,
        input_payload, context_payload, result_payload, generated_at
      )
      values (
        $1, $2, $3::uuid, 'generated', 'local_rules', $4, $5,
        $6::jsonb, $7::jsonb, $8::jsonb, now()
      )
      returning id, tenant_id as "tenantId", user_id as "userId", region_id as "regionId",
                status, provider, model, prompt_version as "promptVersion",
                input_payload as "inputPayload", context_payload as "contextPayload",
                result_payload as "resultPayload", error_message as "errorMessage",
                generated_at as "generatedAt", created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        tenantId,
        userId,
        regionId,
        LOCAL_MODEL,
        PROMPT_VERSION,
        JSON.stringify(input),
        JSON.stringify(contextPayload),
        JSON.stringify(resultPayload),
      ],
    );
    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'ai.school_recommendation.generated', 'ai_recommendation_report', $3, $4::jsonb)
      `,
      [
        tenantId,
        userId,
        result.rows[0].id,
        JSON.stringify({
          provider: 'local_rules',
          model: LOCAL_MODEL,
          promptVersion: PROMPT_VERSION,
          regionId,
          scorelineRecordCount: records.length,
          recommendationCount: candidates.length,
        }),
      ],
    );
    return result.rows[0];
  });

  return { item };
}

export async function schoolRecommendationReportsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 20, 100);
  const regionId = stringParam(ctx, 'regionId');

  const items = await query(
    `
    ${reportSelectSql()}
    where tenant_id = $1
      and user_id = $2
      and ($3::uuid is null or region_id = $3::uuid)
    order by created_at desc
    limit $4
    `,
    [tenantId, userId, regionId || null, limit],
  );
  return { items };
}

export async function schoolRecommendationReportDetailRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const reportId = stringParam(ctx, 'reportId');
  if (!reportId) throw new HttpError(400, 'reportId is required', 'AI_REPORT_ID_REQUIRED');

  const item = await queryOne(
    `
    ${reportSelectSql()}
    where tenant_id = $1 and user_id = $2 and id = $3::uuid
    limit 1
    `,
    [tenantId, userId, reportId],
  );
  if (!item) throw new HttpError(404, 'AI recommendation report not found', 'AI_REPORT_NOT_FOUND');
  return { item };
}

export async function schoolRecommendationReportExportRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const reportId = stringParam(ctx, 'reportId');
  const format = reportExportFormat(stringParam(ctx, 'format'));
  if (!reportId) throw new HttpError(400, 'reportId is required', 'AI_REPORT_ID_REQUIRED');

  const item = await queryOne<SchoolRecommendationReportRow>(
    `
    ${reportSelectSql()}
    where tenant_id = $1 and user_id = $2 and id = $3::uuid and status = 'generated'
    limit 1
    `,
    [tenantId, userId, reportId],
  );
  if (!item) throw new HttpError(404, 'AI recommendation report not found', 'AI_REPORT_NOT_FOUND');

  const exportItem = buildReportExport(item, format);
  await query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2, 'ai.school_recommendation.exported', 'ai_recommendation_report', $3, $4::jsonb)
    `,
    [
      tenantId,
      userId,
      item.id,
      JSON.stringify({
        format,
        sha256: exportItem.sha256,
        sizeBytes: exportItem.sizeBytes,
      }),
    ],
  );

  return { item: exportItem };
}
