`app/api/revenue/route.ts`

98 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { verifyAuth } from '@/lib/auth';

export async function GET(request: NextRequest) {
  const user = verifyAuth(request);
  if (!user) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  try {
    const statusFilter = request.nextUrl.searchParams.get('status');

    let sql = `
      SELECT r.id, r.match_id, r.org_id, r.granter_id,
             r.fee_type, r.flat_fee, r.percentage_rate,
             r.grant_amount, r.commission_amount,
             r.status, r.invoice_date, r.paid_date, r.notes,
             r.created_at, r.updated_at,
             o.name as org_name,
             g.name as granter_name,
             CASE WHEN m.id IS NOT NULL THEN c.title ELSE NULL END as cause_title
      FROM revenue r
      LEFT JOIN organizations o ON o.id = r.org_id
      LEFT JOIN granters g ON g.id = r.granter_id
      LEFT JOIN matches m ON m.id = r.match_id
      LEFT JOIN causes c ON m.id IS NOT NULL AND c.id = m.cause_id
    `;
    const params: unknown[] = [];

    if (statusFilter) {
      params.push(statusFilter);
      sql += ` WHERE r.status = $${params.length}`;
    }

    sql += ' ORDER BY r.created_at DESC LIMIT 100';

    const result = await query(sql, params);

    // Calculate stats
    const statsRes = await query(`
      SELECT
        COALESCE(SUM(COALESCE(commission_amount, 0) + COALESCE(flat_fee, 0)), 0)::numeric as total,
        COALESCE(SUM(CASE WHEN status = 'pending' THEN COALESCE(commission_amount, 0) + COALESCE(flat_fee, 0) ELSE 0 END), 0)::numeric as pending,
        COALESCE(SUM(CASE WHEN status = 'invoiced' THEN COALESCE(commission_amount, 0) + COALESCE(flat_fee, 0) ELSE 0 END), 0)::numeric as invoiced,
        COALESCE(SUM(CASE WHEN status = 'paid' THEN COALESCE(commission_amount, 0) + COALESCE(flat_fee, 0) ELSE 0 END), 0)::numeric as paid
      FROM revenue
    `);

    return NextResponse.json({
      revenue: result.rows,
      stats: {
        total: parseFloat(statsRes.rows[0].total) || 0,
        pending: parseFloat(statsRes.rows[0].pending) || 0,
        invoiced: parseFloat(statsRes.rows[0].invoiced) || 0,
        paid: parseFloat(statsRes.rows[0].paid) || 0,
      },
    });
  } catch (err) {
    console.error('[revenue] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch revenue' }, { status: 500 });
  }
}

export async function POST(request: NextRequest) {
  const user = verifyAuth(request);
  if (!user) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  try {
    const body = await request.json();
    const { match_id, org_id, granter_id, fee_type, flat_fee, percentage_rate, grant_amount, notes } = body;

    if (!fee_type) {
      return NextResponse.json({ error: 'fee_type is required' }, { status: 400 });
    }

    // Calculate commission
    let commission = flat_fee || 0;
    if (percentage_rate && grant_amount) {
      commission += (percentage_rate / 100) * grant_amount;
    }

    const result = await query(
      `INSERT INTO revenue (match_id, org_id, granter_id, fee_type, flat_fee, percentage_rate, grant_amount, commission_amount, notes)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
       RETURNING *`,
      [match_id || null, org_id || null, granter_id || null, fee_type, flat_fee || 0, percentage_rate || 0, grant_amount || null, commission, notes || null],
    );

    return NextResponse.json({ revenue: result.rows[0] }, { status: 201 });
  } catch (err) {
    console.error('[revenue] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to create revenue entry' }, { status: 500 });
  }
}