`app/api/grants/route.ts`

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

/**
 * GET /api/grants
 * List grants with optional filters:
 *   ?status=        — filter by status (researching, preparing, submitted, awarded, declined, expired)
 *   ?priority=      — filter by priority (high, medium, low)
 *   ?cycle=         — filter by cycle (annual, biannual, rolling, one-time)
 *   ?is_bookmarked= — filter by bookmarked flag (true/false)
 *   ?upcoming=      — if true, only grants with deadline in the future, sorted by deadline ASC
 *   ?search=        — ILIKE search on title, funder, description
 * Default sort: deadline ASC for upcoming=true, updated_at DESC otherwise.
 */
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 status = searchParams.get('status');
    const priority = searchParams.get('priority');
    const cycle = searchParams.get('cycle');
    const isBookmarked = searchParams.get('is_bookmarked');
    const upcoming = searchParams.get('upcoming');
    const search = searchParams.get('search');

    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

    const conditions: string[] = [];
    const values: unknown[] = [];
    let paramIndex = 1;

    if (orgId) {
      conditions.push(`org_id = $${paramIndex}`);
      values.push(orgId);
      paramIndex++;
    }

    if (status) {
      conditions.push(`status = $${paramIndex}`);
      values.push(status);
      paramIndex++;
    }

    if (priority) {
      conditions.push(`priority = $${paramIndex}`);
      values.push(priority);
      paramIndex++;
    }

    if (cycle) {
      conditions.push(`cycle = $${paramIndex}`);
      values.push(cycle);
      paramIndex++;
    }

    if (isBookmarked !== null && isBookmarked !== undefined && isBookmarked !== '') {
      conditions.push(`is_bookmarked = $${paramIndex}`);
      values.push(isBookmarked === 'true');
      paramIndex++;
    }

    if (upcoming === 'true') {
      conditions.push(`deadline > NOW()`);
    }

    if (search) {
      conditions.push(
        `(title ILIKE $${paramIndex} OR funder ILIKE $${paramIndex} OR description ILIKE $${paramIndex})`
      );
      values.push(`%${search}%`);
      paramIndex++;
    }

    let limit = parseInt(searchParams.get('limit') || '50', 10);
    if (isNaN(limit) || limit < 1) limit = 50;
    if (limit > 200) limit = 200;

    let offset = parseInt(searchParams.get('offset') || '0', 10);
    if (isNaN(offset) || offset < 0) offset = 0;

    const whereClause =
      conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const orderClause =
      upcoming === 'true'
        ? 'ORDER BY deadline ASC NULLS LAST'
        : 'ORDER BY updated_at DESC';

    const countResult = await query(
      `SELECT COUNT(*)::int AS total FROM grants ${whereClause}`,
      values
    );
    const total = countResult.rows[0]?.total ?? 0;

    const result = await query(
      `SELECT * FROM grants ${whereClause} ${orderClause}
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      [...values, limit, offset]
    );

    return NextResponse.json({ grants: result.rows, total, limit, offset });
  } catch (err) {
    console.error('[api/grants] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch grants' }, { status: 500 });
  }
}

/**
 * POST /api/grants
 * Create a new grant.
 * Body: { title, funder, funder_url?, amount_min?, amount_max?, description?, eligibility?,
 *         focus_areas?, application_url?, deadline?, notification_date?, grant_period_start?,
 *         grant_period_end?, cycle?, ai_fit_score?, ai_suggestion?, ai_application_tips?,
 *         status?, priority?, notes?, applied_at?, amount_requested?, amount_awarded?,
 *         tags?, is_bookmarked? }
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const body = await request.json();

    if (!body.title || typeof body.title !== 'string' || body.title.trim() === '') {
      return NextResponse.json({ error: 'title is required' }, { status: 400 });
    }

    if (!body.funder || typeof body.funder !== 'string' || body.funder.trim() === '') {
      return NextResponse.json({ error: 'funder is required' }, { status: 400 });
    }

    const validStatuses = ['researching', 'preparing', 'submitted', 'approved', 'rejected', 'awarded', 'declined', 'expired'];
    const status = validStatuses.includes(body.status) ? body.status : 'researching';

    const validPriorities = ['high', 'medium', 'low'];
    const priority = validPriorities.includes(body.priority) ? body.priority : 'medium';

    const validCycles = ['annual', 'biannual', 'rolling', 'one-time'];
    const cycle = validCycles.includes(body.cycle) ? body.cycle : null;

    // Parse arrays
    let focusAreas: string[] = [];
    if (Array.isArray(body.focus_areas)) {
      focusAreas = body.focus_areas.map((t: string) => String(t).trim()).filter(Boolean);
    } else if (typeof body.focus_areas === 'string' && body.focus_areas.trim()) {
      focusAreas = body.focus_areas.split(',').map((t: string) => t.trim()).filter(Boolean);
    }

    let tags: string[] = [];
    if (Array.isArray(body.tags)) {
      tags = body.tags.map((t: string) => String(t).trim()).filter(Boolean);
    } else if (typeof body.tags === 'string' && body.tags.trim()) {
      tags = body.tags.split(',').map((t: string) => t.trim()).filter(Boolean);
    }

    const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;

    const result = await query(
      `INSERT INTO grants
         (title, funder, funder_url, amount_min, amount_max, description, eligibility,
          focus_areas, application_url, deadline, notification_date, grant_period_start,
          grant_period_end, cycle, ai_fit_score, ai_suggestion, ai_application_tips,
          status, priority, notes, applied_at, amount_requested, amount_awarded,
          tags, is_bookmarked, org_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26)
       RETURNING *`,
      [
        body.title.trim(),
        body.funder.trim(),
        body.funder_url || null,
        body.amount_min != null ? Number(body.amount_min) : null,
        body.amount_max != null ? Number(body.amount_max) : null,
        body.description || null,
        body.eligibility || null,
        focusAreas,
        body.application_url || null,
        body.deadline || null,
        body.notification_date || null,
        body.grant_period_start || null,
        body.grant_period_end || null,
        cycle,
        body.ai_fit_score != null ? Number(body.ai_fit_score) : null,
        body.ai_suggestion || null,
        body.ai_application_tips || null,
        status,
        priority,
        body.notes || null,
        body.applied_at || null,
        body.amount_requested != null ? Number(body.amount_requested) : null,
        body.amount_awarded != null ? Number(body.amount_awarded) : null,
        tags,
        body.is_bookmarked === true,
        orgId || null,
      ]
    );

    const grant = result.rows[0];

    const ip =
      request.headers.get('x-forwarded-for') ||
      request.headers.get('x-real-ip') ||
      undefined;
    await auditLog(
      'grant.created',
      'grant',
      grant.id,
      { title: grant.title, funder: grant.funder, status: grant.status },
      ip
    );

    return NextResponse.json({ grant }, { status: 201 });
  } catch (err) {
    console.error('[api/grants] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to create grant' }, { status: 500 });
  }
}