[Phase 4] Enhanced file statistics with origin breakdown

Description

Enhance the file details page to show detailed statistics about segment origins and processing sources.

Dependencies

None - Can be implemented independently after Phase 1

Requirements

Location: Enhance existing apps/web/src/app/(authenticated)/files/[id]/page.tsx

New Statistics Section:

Display breakdown of segments by origin:

• MT (Machine Translation)
• TM (Translation Memory)
• Manual translations
• Other sources

Component: apps/web/src/components/files/OriginBreakdownCard.tsx

interface OriginBreakdown {
  origin: string;
  count: number;
  percentage: number;
  avgScore: number | null;
  avgQeScore: number | null;
}

export function OriginBreakdownCard({ fileId }: { fileId: string }) {
  // Server Component - fetch data directly
  const supabase = createRouteHandlerClient();
  const { data } = await supabase.rpc('get_origin_breakdown', { 
    p_file_id: fileId 
  });
  
  return (
    <div className="bg-white rounded-lg shadow p-6">
      <h3 className="text-lg font-semibold mb-4">Segment Origins</h3>
      <div className="space-y-3">
        {data?.map(item => (
          <div key={item.origin} className="flex items-center justify-between">
            <div>
              <span className="font-medium">{item.origin || 'Unknown'}</span>
              <span className="text-sm text-gray-500 ml-2">
                ({item.count} segments, {item.percentage.toFixed(1)}%)
              </span>
            </div>
            <div className="text-right">
              {item.avgScore && (
                <div className="text-sm">
                  TM: {item.avgScore.toFixed(1)}%
                </div>
              )}
              {item.avgQeScore && (
                <div className="text-sm">
                  QE: {item.avgQeScore.toFixed(1)}%
                </div>
              )}
            </div>
          </div>
        ))}
      </div>
    </div>
  );
}

Required RPC:

CREATE OR REPLACE FUNCTION get_origin_breakdown(p_file_id UUID)
RETURNS TABLE (
  origin TEXT,
  count BIGINT,
  percentage NUMERIC,
  avg_score NUMERIC,
  avg_qe_score NUMERIC
) SECURITY DEFINER AS $$
DECLARE
  total_segments BIGINT;
BEGIN
  -- Verify file access via RLS
  IF NOT EXISTS (
    SELECT 1 FROM files 
    WHERE id = p_file_id AND created_by = auth.uid()
  ) THEN
    RAISE EXCEPTION 'Unauthorized or file not found';
  END IF;
  
  -- Get total count
  SELECT COUNT(*) INTO total_segments
  FROM segments WHERE file_id = p_file_id;
  
  RETURN QUERY
  SELECT 
    COALESCE(s.origin, 'Unknown') as origin,
    COUNT(*)::BIGINT as count,
    (COUNT(*)::NUMERIC / total_segments * 100) as percentage,
    AVG(s.score) as avg_score,
    AVG((r.result->>'qe_score')::NUMERIC) as avg_qe_score
  FROM segments s
  LEFT JOIN results r ON r.segment_id = s.id
  WHERE s.file_id = p_file_id
  GROUP BY s.origin
  ORDER BY count DESC;
END;
$$ LANGUAGE plpgsql;

Integration

Add to file header alongside existing stats:

• Total segments
• Approved segments
• NEW: Origin breakdown expandable section
• NEW: Average scores by origin

Acceptance Criteria

• [ ] Origin breakdown displays correctly
• [ ] Percentages sum to 100%
• [ ] Handles files with no origin data gracefully
• [ ] Average scores display only when available
• [ ] RLS enforced (users only see their own files)

Testing

• [ ] Test with files having multiple origins
• [ ] Test with files having null/empty origins
• [ ] Verify percentages are accurate
• [ ] Test with large files (1000+ segments)

Phase

Phase 4: Enhanced Processing Metrics