`app/api/xsessions/route.ts`

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

const GEMINI_KEY = process.env.GEMINI_API_KEY || '';
const GEMINI_URL = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_KEY}`;

/**
 * GET /api/xsessions
 * Fetch x_sessions with post counts, ordered by created_at DESC.
 * Optional: ?status=done|pending|running|error
 */
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 conditions: string[] = [];
    const values: unknown[] = [];
    let paramIndex = 1;

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

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

    const result = await query(
      `SELECT s.*,
              COUNT(p.id)::int AS actual_post_count,
              COUNT(p.id) FILTER (WHERE p.is_used = true)::int AS used_post_count
       FROM x_sessions s
       LEFT JOIN x_posts p ON p.x_session_id = s.id
       ${whereClause}
       GROUP BY s.id
       ORDER BY s.created_at DESC`,
      values
    );

    // Also get aggregate stats
    const statsResult = await query(
      `SELECT
         COUNT(DISTINCT s.id)::int AS total_sessions,
         COUNT(p.id)::int AS total_posts,
         COUNT(p.id) FILTER (WHERE p.is_used = true)::int AS posts_used
       FROM x_sessions s
       LEFT JOIN x_posts p ON p.x_session_id = s.id`
    );

    return NextResponse.json({
      sessions: result.rows,
      stats: statsResult.rows[0],
    });
  } catch (err) {
    console.error('[api/xsessions] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to fetch X sessions' }, { status: 500 });
  }
}

/**
 * POST /api/xsessions
 * Create a new X session. Use Gemini to generate sample posts.
 * Body: { query_params: { terms: string } }
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const client = await getClient();

  try {
    const body = await request.json();
    const queryParams = body.query_params || { terms: 'advocacy' };
    const searchTerms = queryParams.terms || 'advocacy';

    await client.query('BEGIN');

    // Create the session
    const sessionResult = await client.query(
      `INSERT INTO x_sessions (query_params, status)
       VALUES ($1, 'running')
       RETURNING *`,
      [JSON.stringify(queryParams)]
    );
    const session = sessionResult.rows[0];

    // Generate posts with Gemini
    const prompt = `Generate 10 realistic Twitter/X posts about "${searchTerms}" related to nonprofit advocacy and public policy.

Return a JSON array of objects with these fields:
- author_handle: realistic Twitter handle (without @)
- author_name: realistic full name
- content: tweet text (max 280 chars, include hashtags naturally)
- posted_at: ISO date string within the last 48 hours from now (${new Date().toISOString()})
- metrics: { likes: number, retweets: number, replies: number, views: number }
- hashtags: array of hashtags used (without #)
- has_media: boolean

Make the posts diverse - mix of personal stories, policy commentary, news sharing, calls to action, and data-driven takes. Include a range of engagement levels. Some should have high engagement, some moderate, some low.

Return ONLY the JSON array, no markdown fences or other text.`;

    const geminiRes = await fetch(GEMINI_URL, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        contents: [{ parts: [{ text: prompt }] }],
        generationConfig: { temperature: 0.9, maxOutputTokens: 4096 },
      }),
    });

    let posts: Array<{
      author_handle: string;
      author_name: string;
      content: string;
      posted_at: string;
      metrics: Record<string, number>;
      hashtags: string[];
      has_media: boolean;
    }> = [];

    if (geminiRes.ok) {
      const geminiData = await geminiRes.json();
      const rawText = geminiData.candidates?.[0]?.content?.parts?.[0]?.text || '[]';
      // Strip markdown fences
      const cleaned = rawText.replace(/```(?:json)?\s*/g, '').replace(/```\s*/g, '').trim();
      try {
        posts = JSON.parse(cleaned);
      } catch {
        console.error('[xsessions] Failed to parse Gemini response:', cleaned.slice(0, 200));
        posts = [];
      }
    }

    // Insert posts
    let insertedCount = 0;
    for (let i = 0; i < posts.length; i++) {
      const p = posts[i];
      try {
        await client.query(
          `INSERT INTO x_posts (x_session_id, x_post_id, author_handle, author_name, content, posted_at, metrics, hashtags, media_urls)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
          [
            session.id,
            `gen_${session.id.slice(0, 8)}_${i}`,
            p.author_handle || 'unknown',
            p.author_name || 'Unknown User',
            p.content || '',
            p.posted_at || new Date().toISOString(),
            JSON.stringify(p.metrics || {}),
            p.hashtags || [],
            p.has_media ? ['https://pbs.twimg.com/media/placeholder.jpg'] : [],
          ]
        );
        insertedCount++;
      } catch (insertErr) {
        console.error('[xsessions] Post insert error:', (insertErr as Error).message);
      }
    }

    // Update session
    await client.query(
      `UPDATE x_sessions SET status = 'done', post_count = $1, ended_at = now() WHERE id = $2`,
      [insertedCount, session.id]
    );

    await client.query('COMMIT');

    // Fetch the completed session with posts
    const finalResult = await query(
      `SELECT * FROM x_sessions WHERE id = $1`, [session.id]
    );
    const postsResult = await query(
      `SELECT * FROM x_posts WHERE x_session_id = $1 ORDER BY posted_at DESC`, [session.id]
    );

    return NextResponse.json(
      {
        session: { ...finalResult.rows[0], posts: postsResult.rows },
      },
      { status: 201 }
    );
  } catch (err) {
    await client.query('ROLLBACK');
    // Try to mark session as error
    console.error('[api/xsessions] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to create X session' }, { status: 500 });
  } finally {
    client.release();
  }
}