`app/api/email-analyzer/universal-references/route.ts`

200 lines
/**
 * Universal References for the Email Analyzer.
 *
 *   GET    /api/email-analyzer/universal-references        — list all active refs
 *   GET    /api/email-analyzer/universal-references?q=foo  — trigram search
 *   POST   /api/email-analyzer/universal-references        — create
 *   PATCH  /api/email-analyzer/universal-references        — toggle is_active / edit
 *   DELETE /api/email-analyzer/universal-references?id=xxx — remove
 *
 * "Universal" = scoped per org, auto-injected into every rewrite's reference
 * context (see app/api/email-analyzer/rewrite/route.ts). Not per-session.
 */

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

type Role = 'admin' | 'staff';
const ROLES: Role[] = ['admin', 'staff'];

export async function GET(request: NextRequest) {
  const auth = requireRole(request, ...ROLES);
  if (auth instanceof NextResponse) return auth;

  try {
    const { searchParams } = new URL(request.url);
    const q = (searchParams.get('q') || '').trim();
    const activeOnly = searchParams.get('active') !== 'false';

    const conditions: string[] = [];
    const params: unknown[] = [];

    if (activeOnly) conditions.push(`is_active = TRUE`);
    if (q) {
      params.push(`%${q}%`);
      conditions.push(
        `(title ILIKE $${params.length} OR content ILIKE $${params.length})`,
      );
    }

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

    const { rows } = await query(
      `SELECT id, org_id, username, title, content, tags, tone, score,
              source_session_id, is_active, created_at, updated_at
         FROM email_analyzer_universal_refs
         ${where}
         ORDER BY is_active DESC, created_at DESC
         LIMIT 500`,
      params,
    );

    return NextResponse.json({ references: rows });
  } catch (err) {
    console.error('[universal-refs] GET error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to load universal references' }, { status: 500 });
  }
}

export async function POST(request: NextRequest) {
  const auth = requireRole(request, ...ROLES);
  if (auth instanceof NextResponse) return auth;

  try {
    const body = await request.json();
    const {
      title,
      content,
      tags,
      tone,
      score,
      source_session_id,
    } = body as {
      title?: string;
      content?: string;
      tags?: string[];
      tone?: string;
      score?: number;
      source_session_id?: string;
    };

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

    const { rows } = await query(
      `INSERT INTO email_analyzer_universal_refs
         (username, title, content, tags, tone, score, source_session_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       RETURNING *`,
      [
        auth.username ?? null,
        title.slice(0, 200),
        content,
        Array.isArray(tags) ? tags.slice(0, 20) : [],
        tone ?? null,
        typeof score === 'number' ? Math.max(0, Math.min(100, Math.round(score))) : null,
        source_session_id ?? null,
      ],
    );

    return NextResponse.json({ reference: rows[0] }, { status: 201 });
  } catch (err) {
    console.error('[universal-refs] POST error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to save universal reference' }, { status: 500 });
  }
}

export async function PATCH(request: NextRequest) {
  const auth = requireRole(request, ...ROLES);
  if (auth instanceof NextResponse) return auth;

  try {
    const body = await request.json();
    const { id, is_active, title, content, tags, tone } = body as {
      id?: string;
      is_active?: boolean;
      title?: string;
      content?: string;
      tags?: string[];
      tone?: string;
    };

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

    const sets: string[] = [];
    const params: unknown[] = [];
    if (is_active !== undefined) {
      params.push(is_active);
      sets.push(`is_active = $${params.length}`);
    }
    if (title !== undefined) {
      params.push(title.slice(0, 200));
      sets.push(`title = $${params.length}`);
    }
    if (content !== undefined) {
      params.push(content);
      sets.push(`content = $${params.length}`);
    }
    if (tags !== undefined) {
      params.push(Array.isArray(tags) ? tags.slice(0, 20) : []);
      sets.push(`tags = $${params.length}`);
    }
    if (tone !== undefined) {
      params.push(tone);
      sets.push(`tone = $${params.length}`);
    }

    if (sets.length === 0) {
      return NextResponse.json({ error: 'nothing to update' }, { status: 400 });
    }

    sets.push(`updated_at = NOW()`);
    params.push(id);

    const { rows, rowCount } = await query(
      `UPDATE email_analyzer_universal_refs
          SET ${sets.join(', ')}
        WHERE id = $${params.length}
        RETURNING *`,
      params,
    );

    if (!rowCount) {
      return NextResponse.json({ error: 'not found' }, { status: 404 });
    }

    return NextResponse.json({ reference: rows[0] });
  } catch (err) {
    console.error('[universal-refs] PATCH error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to update' }, { status: 500 });
  }
}

export async function DELETE(request: NextRequest) {
  const auth = requireRole(request, ...ROLES);
  if (auth instanceof NextResponse) return auth;

  try {
    const id = new URL(request.url).searchParams.get('id');
    if (!id) return NextResponse.json({ error: 'id required' }, { status: 400 });

    const { rowCount } = await query(
      `DELETE FROM email_analyzer_universal_refs WHERE id = $1`,
      [id],
    );
    if (!rowCount) return NextResponse.json({ error: 'not found' }, { status: 404 });

    return NextResponse.json({ deleted: true, id });
  } catch (err) {
    console.error('[universal-refs] DELETE error:', (err as Error).message);
    return NextResponse.json({ error: 'Failed to delete' }, { status: 500 });
  }
}