← back to Norma

app/api/onboard/stats/route.ts

99 lines

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';

// ─── GET /api/onboard/stats ───────────────────────────────────────────────────

async function safeQuery<T extends Record<string, unknown>>(
  sql: string,
  params?: unknown[],
): Promise<T[]> {
  try {
    const { rows } = await query<T>(sql, params);
    return rows;
  } catch (err: unknown) {
    const msg = (err as Error).message ?? '';
    if (msg.includes('does not exist') || msg.includes('undefined_table')) {
      return [];
    }
    throw err;
  }
}

export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
    const orgFilter = orgId ? 'WHERE org_id = $1' : '';
    const orgParams = orgId ? [orgId] : [];

    // Status counts from onboard_discoveries
    const statusRows = await safeQuery<{ status: string; count: string }>(
      `SELECT COALESCE(status, 'discovered') AS status, COUNT(*) AS count
       FROM onboard_discoveries
       ${orgFilter}
       GROUP BY status`,
      orgParams,
    );

    const statusCounts: Record<string, number> = {
      discovered: 0,
      contacted: 0,
      onboarded: 0,
      rejected: 0,
    };
    let total = 0;
    for (const r of statusRows) {
      const n = parseInt(r.count, 10);
      statusCounts[r.status] = n;
      total += n;
    }

    // Count of mind_map_links (cross-modal connections)
    const linkRows = await safeQuery<{ count: string }>(
      `SELECT COUNT(*) AS count FROM mind_map_links`,
    );
    const totalLinks = parseInt(linkRows[0]?.count ?? '0', 10);

    // Avg match score for onboarded orgs
    const avgRows = await safeQuery<{ avg_score: string | null }>(
      `SELECT AVG(match_score)::numeric(5,2) AS avg_score
       FROM onboard_discoveries
       WHERE status = 'onboarded'${orgId ? ' AND org_id = $1' : ''}`,
      orgParams,
    );
    const avgMatchScore = parseFloat(avgRows[0]?.avg_score ?? '0') || 0;

    // Top trigger topics
    const topicRows = await safeQuery<{ trigger_topic: string; count: string }>(
      `SELECT trigger_topic, COUNT(*) AS count
       FROM onboard_discoveries
       WHERE trigger_topic IS NOT NULL${orgId ? ' AND org_id = $1' : ''}
       GROUP BY trigger_topic
       ORDER BY count DESC
       LIMIT 5`,
      orgParams,
    );

    return NextResponse.json({
      discovered: statusCounts.discovered,
      contacted: statusCounts.contacted,
      onboarded: statusCounts.onboarded,
      rejected: statusCounts.rejected,
      total,
      total_links: totalLinks,
      avg_match_score_onboarded: avgMatchScore,
      top_trigger_topics: topicRows.map((r) => ({
        topic: r.trigger_topic,
        count: parseInt(r.count, 10),
      })),
    });
  } catch (err: unknown) {
    console.error('[api/onboard/stats] Error:', (err as Error).message);
    return NextResponse.json({ error: (err as Error).message }, { status: 500 });
  }
}