`app/api/connections/entity/[type]/[id]/route.ts`

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

/* ═══════════════════════════════════════════════════════════════════════════
   GET /api/connections/entity/[type]/[id]

   Returns all connections for a specific entity.
   Type must be: politician, organization, journalist, pulse_topic, district

   Response:
   {
     entity: { id, type, name, ...details },
     connections: [{ entity: {...}, linkType, weight, metadata }],
     stats: { totalConnections, byType: {organization: 8, journalist: 5} }
   }
   ═══════════════════════════════════════════════════════════════════════════ */

const VALID_TYPES = ['politician', 'organization', 'journalist', 'pulse_topic', 'district'];

// Fetch entity details by type
async function fetchEntity(type: string, id: string): Promise<Record<string, unknown> | null> {
  switch (type) {
    case 'politician': {
      const res = await query(
        `SELECT id, name, title, party, state, district, committees, caucuses,
                position_student_debt, position_education, phone, email, website, photo_url
         FROM politicians WHERE id = $1`,
        [id],
      );
      return res.rows[0] ? { ...res.rows[0], type: 'politician' } : null;
    }
    case 'organization': {
      const res = await query(
        `SELECT id, name, type, category, state, city, key_issues, political_leaning,
                website, description, mission, annual_revenue, staff_size
         FROM organizations WHERE id = $1`,
        [id],
      );
      return res.rows[0] ? { ...res.rows[0], type: 'organization' } : null;
    }
    case 'journalist': {
      const res = await query(
        `SELECT j.id, j.name, j.outlet, j.beat, j.bio, j.twitter_handle, j.email, j.tags,
                COUNT(a.id) AS article_count
         FROM journalists j
         LEFT JOIN journalist_articles a ON a.journalist_id = j.id
         WHERE j.id = $1
         GROUP BY j.id`,
        [id],
      );
      return res.rows[0] ? { ...res.rows[0], type: 'journalist' } : null;
    }
    case 'pulse_topic': {
      const res = await query(
        `SELECT id, name, category, urgency, description FROM pulse_topics WHERE id = $1`,
        [id],
      );
      return res.rows[0] ? { ...res.rows[0], type: 'pulse_topic' } : null;
    }
    case 'district': {
      const res = await query(
        `SELECT id, state_abbr, district_number, representative_name, representative_party,
                opportunity_score, total_population, avg_distress_score,
                community_college_count, total_cc_enrollment
         FROM congressional_districts WHERE id = $1`,
        [id],
      );
      return res.rows[0] ? { ...res.rows[0], type: 'district' } : null;
    }
    default:
      return null;
  }
}

// Fetch connected entity details in bulk
async function fetchConnectedEntities(
  entities: Array<{ type: string; id: string }>,
): Promise<Map<string, Record<string, unknown>>> {
  const map = new Map<string, Record<string, unknown>>();
  if (entities.length === 0) return map;

  // Group by type
  const byType: Record<string, string[]> = {};
  for (const e of entities) {
    if (!byType[e.type]) byType[e.type] = [];
    byType[e.type].push(e.id);
  }

  const queries: Promise<void>[] = [];

  if (byType.politician?.length) {
    queries.push(
      query(
        `SELECT id, name, party, state, committees, photo_url FROM politicians WHERE id = ANY($1)`,
        [byType.politician],
      ).then((res) => {
        for (const r of res.rows) map.set(r.id, { ...r, type: 'politician' });
      }),
    );
  }

  if (byType.organization?.length) {
    queries.push(
      query(
        `SELECT id, name, type, category, state, key_issues FROM organizations WHERE id = ANY($1)`,
        [byType.organization],
      ).then((res) => {
        for (const r of res.rows) map.set(r.id, { ...r, type: 'organization' });
      }),
    );
  }

  if (byType.journalist?.length) {
    queries.push(
      query(
        `SELECT id, name, outlet, beat FROM journalists WHERE id = ANY($1)`,
        [byType.journalist],
      ).then((res) => {
        for (const r of res.rows) map.set(r.id, { ...r, type: 'journalist' });
      }),
    );
  }

  if (byType.pulse_topic?.length) {
    queries.push(
      query(
        `SELECT id, name, category, urgency FROM pulse_topics WHERE id = ANY($1)`,
        [byType.pulse_topic],
      ).then((res) => {
        for (const r of res.rows) map.set(r.id, { ...r, type: 'pulse_topic' });
      }),
    );
  }

  if (byType.district?.length) {
    queries.push(
      query(
        `SELECT id, state_abbr, district_number, representative_name, opportunity_score
         FROM congressional_districts WHERE id = ANY($1)`,
        [byType.district],
      ).then((res) => {
        for (const r of res.rows) map.set(r.id, { ...r, type: 'district', label: `${r.state_abbr}-${r.district_number}` });
      }),
    );
  }

  await Promise.all(queries);
  return map;
}

export async function GET(
  request: NextRequest,
  { params }: { params: Promise<{ type: string; id: string }> },
) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const { type, id } = await params;

  if (!VALID_TYPES.includes(type)) {
    return NextResponse.json(
      { error: `Invalid entity type: ${type}. Must be one of: ${VALID_TYPES.join(', ')}` },
      { status: 400 },
    );
  }

  // Validate UUID format
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
  if (!uuidRegex.test(id)) {
    return NextResponse.json({ error: 'Invalid entity ID format' }, { status: 400 });
  }

  try {
    // Fetch the main entity
    const entity = await fetchEntity(type, id);
    if (!entity) {
      return NextResponse.json({ error: 'Entity not found' }, { status: 404 });
    }

    // Fetch all links where this entity is source or target
    // Use source_label/target_label for fast label resolution
    const linksResult = await query(
      `SELECT source_type, source_id, source_label, target_type, target_id, target_label,
              link_type, weight, metadata
       FROM mind_map_links
       WHERE (source_type = $1 AND source_id = $2)
          OR (target_type = $1 AND target_id = $2)
       ORDER BY weight DESC
       LIMIT 500`,
      [type, id],
    );

    // Collect connected entity refs (for enriched lookups)
    const connectedRefs: Array<{ type: string; id: string }> = [];
    for (const link of linksResult.rows) {
      if (link.source_id === id) {
        connectedRefs.push({ type: link.target_type, id: link.target_id });
      } else {
        connectedRefs.push({ type: link.source_type, id: link.source_id });
      }
    }

    // Bulk fetch connected entity details (for types we have enriched queries for)
    const entityMap = await fetchConnectedEntities(connectedRefs);

    // Build connections response — use entityMap for enriched data, fall back to link labels
    const connections = linksResult.rows.map((link) => {
      const isSource = link.source_id === id;
      const connectedId = isSource ? link.target_id : link.source_id;
      const connectedType = isSource ? link.target_type : link.source_type;
      const connectedLabel = isSource ? (link.target_label || '') : (link.source_label || '');
      const connectedEntity = entityMap.get(connectedId) || {
        id: connectedId,
        type: connectedType,
        label: connectedLabel || connectedType,
        name: connectedLabel || connectedType,
      };

      return {
        entity: connectedEntity,
        linkType: link.link_type,
        weight: link.weight,
        metadata: link.metadata || {},
      };
    });

    // Build stats
    const byType: Record<string, number> = {};
    for (const c of connections) {
      const t = (c.entity as Record<string, unknown>).type as string;
      byType[t] = (byType[t] || 0) + 1;
    }

    return NextResponse.json({
      entity,
      connections,
      stats: {
        totalConnections: connections.length,
        byType,
      },
    });
  } catch (err) {
    console.error('[connections/entity] Error:', (err as Error).message);
    return NextResponse.json(
      { error: 'Failed to load entity connections' },
      { status: 500 },
    );
  }
}