-- messages table
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  swarm_id UUID REFERENCES swarms(id) ON DELETE CASCADE NOT NULL,
  sender_type TEXT NOT NULL CHECK (sender_type IN ('human', 'agent', 'system')),
  sender_id UUID REFERENCES agents(id),
  content TEXT NOT NULL,
  reasoning TEXT,
  signature TEXT,
  verified BOOLEAN DEFAULT false,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- message_flags table
CREATE TABLE message_flags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id UUID REFERENCES messages(id) ON DELETE CASCADE,
  flagged_by UUID REFERENCES auth.users(id),
  reason TEXT NOT NULL,
  notes TEXT,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- RLS Policies
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view messages in accessible swarms"
  ON messages FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM swarms
      WHERE swarms.id = messages.swarm_id
      AND (
        swarms.user_id = auth.uid() OR
        swarms.visibility = 'public' OR
        EXISTS (
          SELECT 1 FROM swarm_shares
          WHERE swarm_id = swarms.id
          AND shared_with = auth.uid()
        )
      )
    )
  );

const { data: messages, error } = await supabase
  .from('messages')
  .select(`
    *,
    agent:agents (
      id,
      name,
      role,
      avatar
    )
  `)
  .eq('swarm_id', swarmId)
  .order('created_at', { ascending: true });

curl -X GET \
  'https://your-project.supabase.co/rest/v1/messages?swarm_id=eq.SWARM_ID&order=created_at.asc' \
  -H 'Authorization: Bearer YOUR_ACCESS_TOKEN' \
  -H 'apikey: YOUR_ANON_KEY'

{
  "data": [
    {
      "id": "880e8400-e29b-41d4-a716-446655440001",
      "swarm_id": "660e8400-e29b-41d4-a716-446655440001",
      "sender_type": "human",
      "sender_id": null,
      "content": "Can you analyze the market trends for AI in 2024?",
      "reasoning": null,
      "signature": null,
      "verified": false,
      "metadata": {
        "user_id": "550e8400-e29b-41d4-a716-446655440000",
        "client": "web"
      },
      "created_at": "2024-01-15T10:00:00.000Z",
      "agent": null
    },
    {
      "id": "880e8400-e29b-41d4-a716-446655440002",
      "swarm_id": "660e8400-e29b-41d4-a716-446655440001",
      "sender_type": "agent",
      "sender_id": "550e8400-e29b-41d4-a716-446655440001",
      "content": "I'll analyze the current AI market trends for 2024...\n\n## Key Trends\n\n1. **Generative AI Adoption**: Enterprise adoption has grown 250% YoY...\n2. **Edge AI**: Local processing capabilities expanding...",
      "reasoning": "The user is asking about market trends. I should provide a structured analysis covering major developments, market size, and future projections.",
      "signature": "sha256:a1b2c3d4e5f6...",
      "verified": true,
      "metadata": {
        "model": "gpt-4o",
        "tokens_input": 156,
        "tokens_output": 847,
        "latency_ms": 2341,
        "cost_usd": 0.0125
      },
      "created_at": "2024-01-15T10:00:15.000Z",
      "agent": {
        "id": "550e8400-e29b-41d4-a716-446655440001",
        "name": "Research Assistant",
        "role": "Researcher",
        "avatar": "https://api.dicebear.com/7.x/bottts/svg?seed=research"
      }
    }
  ],
  "error": null
}

// Paginated messages
const { data } = await supabase
  .from('messages')
  .select('*')
  .eq('swarm_id', swarmId)
  .order('created_at', { ascending: false })
  .range(0, 49); // Latest 50

// Messages from specific agent
const { data } = await supabase
  .from('messages')
  .select('*')
  .eq('swarm_id', swarmId)
  .eq('sender_id', agentId);

// Messages after timestamp
const { data } = await supabase
  .from('messages')
  .select('*')
  .eq('swarm_id', swarmId)
  .gt('created_at', timestamp);

// Search message content
const { data } = await supabase
  .from('messages')
  .select('*')
  .eq('swarm_id', swarmId)
  .ilike('content', '%keyword%');

const { data: message, error } = await supabase
  .from('messages')
  .insert({
    swarm_id: swarmId,
    sender_type: 'human',
    content: 'Please analyze the Q1 sales data and identify trends.',
    metadata: {
      user_id: user.id,
      client: 'web',
      attachments: []
    }
  })
  .select()
  .single();

{
  "data": {
    "id": "880e8400-e29b-41d4-a716-446655440003",
    "swarm_id": "660e8400-e29b-41d4-a716-446655440001",
    "sender_type": "human",
    "sender_id": null,
    "content": "Please analyze the Q1 sales data...",
    "reasoning": null,
    "signature": null,
    "verified": false,
    "metadata": {
      "user_id": "550e8400-e29b-41d4-a716-446655440000",
      "client": "web"
    },
    "created_at": "2024-01-15T11:00:00.000Z"
  },
  "error": null
}

const response = await fetch(
  `${process.env.NEXT_PUBLIC_SUPABASE_URL}/functions/v1/agent-respond`,
  {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json',
      'apikey': process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
    },
    body: JSON.stringify({
      swarm_id: swarmId,
      message: 'Analyze the Q1 sales data',
      agent_id: agentId,  // Optional: specific agent
      options: {
        temperature: 0.7,
        max_tokens: 4096,
        stream: true
      }
    })
  }
);

const reader = response.body?.getReader();
const decoder = new TextDecoder();

let fullContent = '';

while (reader) {
  const { done, value } = await reader.read();
  if (done) break;

  const chunk = decoder.decode(value);
  const lines = chunk.split('\n');

  for (const line of lines) {
    if (!line.startsWith('data: ')) continue;

    const data = line.slice(6);
    if (data === '[DONE]') {
      console.log('Stream complete');
      continue;
    }

    try {
      const parsed = JSON.parse(data);

      switch (parsed.type) {
        case 'start':
          console.log('Agent:', parsed.agent_name);
          break;
        case 'content':
          fullContent += parsed.content;
          // Update UI with streaming content
          break;
        case 'reasoning':
          console.log('Reasoning:', parsed.reasoning);
          break;
        case 'tool_call':
          console.log('Tool called:', parsed.tool_name);
          break;
        case 'complete':
          console.log('Message ID:', parsed.message_id);
          console.log('Tokens:', parsed.usage);
          break;
        case 'error':
          console.error('Error:', parsed.error);
          break;
      }
    } catch (e) {
      console.error('Parse error:', e);
    }
  }
}

{
  "message_id": "880e8400-e29b-41d4-a716-446655440004",
  "agent_id": "550e8400-e29b-41d4-a716-446655440001",
  "agent_name": "Research Assistant",
  "content": "Based on the Q1 sales data...",
  "reasoning": "I need to analyze the sales figures...",
  "usage": {
    "input_tokens": 234,
    "output_tokens": 1567,
    "total_tokens": 1801
  },
  "latency_ms": 3456,
  "model": "gpt-4o"
}

const channel = supabase
  .channel(`messages:${swarmId}`)
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'messages',
      filter: `swarm_id=eq.${swarmId}`
    },
    (payload) => {
      const newMessage = payload.new;
      console.log('New message:', newMessage);

      // Update UI
      addMessageToUI(newMessage);
    }
  )
  .on(
    'postgres_changes',
    {
      event: 'UPDATE',
      schema: 'public',
      table: 'messages',
      filter: `swarm_id=eq.${swarmId}`
    },
    (payload) => {
      console.log('Message updated:', payload.new);
      updateMessageInUI(payload.new);
    }
  )
  .subscribe();

// Cleanup on unmount
return () => {
  supabase.removeChannel(channel);
};

const channel = supabase.channel(`presence:${swarmId}`);

// Subscribe to typing status
channel.on('presence', { event: 'sync' }, () => {
  const state = channel.presenceState();
  const typingAgents = Object.values(state)
    .flat()
    .filter(p => p.typing);
  updateTypingIndicator(typingAgents);
});

channel.subscribe();

// Set typing status (for agents)
await channel.track({
  agent_id: agentId,
  typing: true
});

const { data: flag, error } = await supabase
  .from('message_flags')
  .insert({
    message_id: messageId,
    reason: 'inaccurate',
    notes: 'The statistics cited appear to be outdated.'
  })
  .select()
  .single();

const { data: flags, error } = await supabase
  .from('message_flags')
  .select(`
    *,
    message:messages (*)
  `)
  .eq('status', 'pending');

const response = await fetch(
  `${SUPABASE_URL}/functions/v1/message-signatures`,
  {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      action: 'verify',
      message_id: messageId
    })
  }
);

const { verified, details } = await response.json();