← back to Norma
app/api/connections/deep-insights/route.ts
370 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
/* ═══════════════════════════════════════════════════════════════════════════
GET /api/connections/deep-insights — Cross-entity intelligence aggregation
Aggregates crisis zones, pressure points, social pulse, connection stats,
and high-debt hotspots into a single Deep Insights dashboard payload.
Query params:
?section=crisisZones,topPressurePoints — return only named sections
(comma-separated; omit for all)
═══════════════════════════════════════════════════════════════════════════ */
// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------
interface CrisisZone {
id: string;
state_abbr: string;
district_number: number;
representative_name: string | null;
representative_party: string | null;
total_complaints: number;
avg_distress_score: number | null;
opportunity_score: number | null;
school_count: number;
complaint_count: number;
avg_school_debt: number | null;
}
interface PressurePoint {
id: string;
name: string;
party: string | null;
state: string | null;
committees: string[];
complaint_links: number;
debt_pressure_links: number;
org_links: number;
press_links: number;
staffer_count: number;
}
interface SocialPulseEntry {
topic_id: string;
topic_name: string;
urgency: string | null;
category: string | null;
mention_count: number;
community_count: number;
news_count: number;
petition_count: number;
grant_count: number;
}
interface LinkTypeStat {
link_type: string;
count: number;
}
type ConnectionStats = LinkTypeStat[];
interface HighDebtHotspot {
id: string;
school_name: string;
state: string | null;
city: string | null;
median_debt: number;
enrollment: number | null;
completion_rate: number | null;
pell_grant_rate: number | null;
representative: string | null;
district_code: string | null;
}
type SectionName =
| 'crisisZones'
| 'topPressurePoints'
| 'socialPulse'
| 'connectionStats'
| 'highDebtHotspots';
interface DeepInsightsResponse {
crisisZones?: CrisisZone[];
topPressurePoints?: PressurePoint[];
socialPulse?: SocialPulseEntry[];
connectionStats?: LinkTypeStat[];
highDebtHotspots?: HighDebtHotspot[];
generatedAt: string;
}
// ---------------------------------------------------------------------------
// Valid section names
// ---------------------------------------------------------------------------
const ALL_SECTIONS: SectionName[] = [
'crisisZones',
'topPressurePoints',
'socialPulse',
'connectionStats',
'highDebtHotspots',
];
function parseSections(param: string | null): Set<SectionName> {
if (!param) return new Set(ALL_SECTIONS);
const requested = param
.split(',')
.map((s) => s.trim())
.filter((s): s is SectionName => ALL_SECTIONS.includes(s as SectionName));
return requested.length > 0 ? new Set(requested) : new Set(ALL_SECTIONS);
}
// ---------------------------------------------------------------------------
// Section fetchers
// ---------------------------------------------------------------------------
async function fetchCrisisZones(): Promise<CrisisZone[]> {
const res = await query<{
id: string;
state_abbr: string;
district_number: number;
representative_name: string | null;
representative_party: string | null;
total_complaints: string;
avg_distress_score: string | null;
opportunity_score: string | null;
school_count: string;
complaint_count: string;
avg_school_debt: string | null;
}>(`
SELECT cd.id, cd.state_abbr, cd.district_number, cd.representative_name, cd.representative_party,
cd.total_complaints, cd.avg_distress_score, cd.opportunity_score,
(SELECT COUNT(*) FROM mind_map_links ml
WHERE ml.target_id = cd.id AND ml.link_type = 'school_in_district') AS school_count,
(SELECT COUNT(*) FROM mind_map_links ml
WHERE ml.target_id = cd.id AND ml.link_type = 'complaint_in_district') AS complaint_count,
(SELECT AVG((ml.metadata->>'median_debt')::numeric) FROM mind_map_links ml
WHERE ml.target_id = cd.id
AND ml.link_type = 'school_in_district'
AND ml.metadata->>'median_debt' IS NOT NULL) AS avg_school_debt
FROM congressional_districts cd
WHERE cd.total_complaints > 0 OR cd.opportunity_score > 50
ORDER BY cd.opportunity_score DESC NULLS LAST
LIMIT 20
`);
return res.rows.map((r) => ({
id: r.id,
state_abbr: r.state_abbr,
district_number: r.district_number,
representative_name: r.representative_name,
representative_party: r.representative_party,
total_complaints: parseInt(r.total_complaints, 10) || 0,
avg_distress_score: r.avg_distress_score ? parseFloat(r.avg_distress_score) : null,
opportunity_score: r.opportunity_score ? parseFloat(r.opportunity_score) : null,
school_count: parseInt(r.school_count, 10) || 0,
complaint_count: parseInt(r.complaint_count, 10) || 0,
avg_school_debt: r.avg_school_debt ? parseFloat(parseFloat(r.avg_school_debt).toFixed(2)) : null,
}));
}
async function fetchTopPressurePoints(): Promise<PressurePoint[]> {
const res = await query<{
id: string;
name: string;
party: string | null;
state: string | null;
committees: string[] | null;
complaint_links: string;
debt_pressure_links: string;
org_links: string;
press_links: string;
staffer_count: string;
}>(`
SELECT p.id, p.name, p.party, p.state, p.committees,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'constituent_complaint') AS complaint_links,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'debt_pressure') AS debt_pressure_links,
(SELECT COUNT(*) FROM mind_map_links WHERE source_id = p.id AND link_type = 'issue_alignment') AS org_links,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'press_coverage') AS press_links,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'works_for') AS staffer_count
FROM politicians p
ORDER BY (
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'constituent_complaint') * 3 +
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'debt_pressure') * 2 +
(SELECT COUNT(*) FROM mind_map_links WHERE source_id = p.id AND link_type = 'issue_alignment') +
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = p.id AND link_type = 'press_coverage') * 2
) DESC
LIMIT 20
`);
return res.rows.map((r) => ({
id: r.id,
name: r.name,
party: r.party,
state: r.state,
committees: r.committees || [],
complaint_links: parseInt(r.complaint_links, 10) || 0,
debt_pressure_links: parseInt(r.debt_pressure_links, 10) || 0,
org_links: parseInt(r.org_links, 10) || 0,
press_links: parseInt(r.press_links, 10) || 0,
staffer_count: parseInt(r.staffer_count, 10) || 0,
}));
}
async function fetchSocialPulse(): Promise<SocialPulseEntry[]> {
const res = await query<{
topic_id: string;
topic_name: string;
urgency: string | null;
category: string | null;
mention_count: string;
community_count: string;
news_count: string;
petition_count: string;
grant_count: string;
}>(`
SELECT pt.id AS topic_id, pt.name AS topic_name, pt.urgency, pt.category,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'social_signal') AS mention_count,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'community_discussion') AS community_count,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'news_coverage') AS news_count,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'petition_advocates') AS petition_count,
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'funding_opportunity') AS grant_count
FROM pulse_topics pt
ORDER BY (
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'social_signal') * 2 +
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'community_discussion') * 2 +
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'news_coverage') * 3 +
(SELECT COUNT(*) FROM mind_map_links WHERE target_id = pt.id AND link_type = 'petition_advocates') * 2
) DESC
`);
return res.rows.map((r) => ({
topic_id: r.topic_id,
topic_name: r.topic_name,
urgency: r.urgency,
category: r.category,
mention_count: parseInt(r.mention_count, 10) || 0,
community_count: parseInt(r.community_count, 10) || 0,
news_count: parseInt(r.news_count, 10) || 0,
petition_count: parseInt(r.petition_count, 10) || 0,
grant_count: parseInt(r.grant_count, 10) || 0,
}));
}
async function fetchConnectionStats(): Promise<LinkTypeStat[]> {
const [byTypeRes, totalLinksRes, totalPolOrgRes] = await Promise.all([
query<{ link_type: string; count: string }>(
`SELECT link_type, COUNT(*) AS count FROM mind_map_links GROUP BY link_type ORDER BY count DESC`,
),
query<{ total: string }>(
`SELECT COUNT(*) AS total FROM mind_map_links`,
),
query<{ total: string }>(
`SELECT COUNT(*) AS total FROM politician_org_links`,
),
]);
// Return as flat array — component iterates with .reduce() and .sort()
return byTypeRes.rows.map((r) => ({
link_type: r.link_type,
count: parseInt(r.count, 10) || 0,
}));
}
async function fetchHighDebtHotspots(): Promise<HighDebtHotspot[]> {
const res = await query<{
id: string;
school_name: string;
state: string | null;
city: string | null;
median_debt: string;
enrollment: string | null;
completion_rate: string | null;
pell_grant_rate: string | null;
representative: string | null;
district_code: string | null;
}>(`
SELECT cs.id, cs.school_name, cs.state, cs.city, cs.median_debt, cs.enrollment, cs.completion_rate, cs.pell_grant_rate,
(SELECT cd.representative_name FROM congressional_districts cd
JOIN district_zips dz ON dz.district_id = cd.id
WHERE dz.zip = LEFT(cs.zip, 5) LIMIT 1) AS representative,
(SELECT cd.state_abbr || '-' || cd.district_number FROM congressional_districts cd
JOIN district_zips dz ON dz.district_id = cd.id
WHERE dz.zip = LEFT(cs.zip, 5) LIMIT 1) AS district_code
FROM college_scorecard cs
WHERE cs.median_debt IS NOT NULL
ORDER BY cs.median_debt DESC
LIMIT 15
`);
return res.rows.map((r) => ({
id: r.id,
school_name: r.school_name,
state: r.state,
city: r.city,
median_debt: parseFloat(r.median_debt),
enrollment: r.enrollment ? parseInt(r.enrollment, 10) : null,
completion_rate: r.completion_rate ? parseFloat(r.completion_rate) : null,
pell_grant_rate: r.pell_grant_rate ? parseFloat(r.pell_grant_rate) : null,
representative: r.representative,
district_code: r.district_code,
}));
}
// ---------------------------------------------------------------------------
// Route handler
// ---------------------------------------------------------------------------
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const { searchParams } = new URL(request.url);
const sections = parseSections(searchParams.get('section'));
// Build a map of section name -> promise, only for requested sections
const fetchers: Partial<Record<SectionName, Promise<unknown>>> = {};
if (sections.has('crisisZones')) {
fetchers.crisisZones = fetchCrisisZones();
}
if (sections.has('topPressurePoints')) {
fetchers.topPressurePoints = fetchTopPressurePoints();
}
if (sections.has('socialPulse')) {
fetchers.socialPulse = fetchSocialPulse();
}
if (sections.has('connectionStats')) {
fetchers.connectionStats = fetchConnectionStats();
}
if (sections.has('highDebtHotspots')) {
fetchers.highDebtHotspots = fetchHighDebtHotspots();
}
// Resolve all requested sections in parallel
const keys = Object.keys(fetchers) as SectionName[];
const values = await Promise.all(keys.map((k) => fetchers[k]));
const result: DeepInsightsResponse = { generatedAt: new Date().toISOString() };
for (let i = 0; i < keys.length; i++) {
const key = keys[i];
if (key === 'crisisZones') {
result.crisisZones = values[i] as CrisisZone[];
} else if (key === 'topPressurePoints') {
result.topPressurePoints = values[i] as PressurePoint[];
} else if (key === 'socialPulse') {
result.socialPulse = values[i] as SocialPulseEntry[];
} else if (key === 'connectionStats') {
result.connectionStats = values[i] as ConnectionStats;
} else if (key === 'highDebtHotspots') {
result.highDebtHotspots = values[i] as HighDebtHotspot[];
}
}
return NextResponse.json(result);
} catch (err) {
console.error('[connections/deep-insights] Error:', (err as Error).message);
return NextResponse.json(
{ error: 'Failed to load deep insights data' },
{ status: 500 },
);
}
}