-- tools table
CREATE TABLE tools (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT DEFAULT 'Custom',
  icon TEXT DEFAULT 'Wrench',
  is_system BOOLEAN DEFAULT false,
  is_custom BOOLEAN DEFAULT true,
  status TEXT DEFAULT 'active',
  input_schema JSONB NOT NULL,
  output_schema JSONB,
  endpoint_url TEXT,
  endpoint_method TEXT DEFAULT 'POST',
  authentication JSONB,
  rate_limit INTEGER,
  timeout_ms INTEGER DEFAULT 30000,
  version TEXT DEFAULT '1.0.0',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- tool_usage table
CREATE TABLE tool_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tool_id UUID REFERENCES tools(id),
  user_id UUID REFERENCES auth.users(id),
  agent_id UUID REFERENCES agents(id),
  swarm_id UUID REFERENCES swarms(id),
  input JSONB,
  output JSONB,
  status TEXT,
  error_message TEXT,
  execution_time_ms INTEGER,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- user_tools configuration table
CREATE TABLE user_tools (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  tool_id UUID REFERENCES tools(id) NOT NULL,
  is_enabled BOOLEAN DEFAULT true,
  configuration JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, tool_id)
);

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

CREATE POLICY "Users can view system tools"
  ON tools FOR SELECT TO authenticated
  USING (is_system = true);

CREATE POLICY "Users can view own custom tools"
  ON tools FOR SELECT TO authenticated
  USING (user_id = auth.uid());

const { data: tools, error } = await supabase
  .from('tools')
  .select('*')
  .order('category')
  .order('name');

{
  "data": [
    {
      "id": "990e8400-e29b-41d4-a716-446655440001",
      "user_id": null,
      "name": "web_search",
      "description": "Search the web using DuckDuckGo for current information. Returns relevant search results with titles, snippets, and URLs.",
      "category": "Research",
      "icon": "Search",
      "is_system": true,
      "is_custom": false,
      "status": "active",
      "input_schema": {
        "type": "object",
        "properties": {
          "query": {
            "type": "string",
            "description": "Search query"
          },
          "max_results": {
            "type": "integer",
            "description": "Maximum results to return",
            "default": 10
          }
        },
        "required": ["query"]
      },
      "output_schema": {
        "type": "object",
        "properties": {
          "results": {
            "type": "array",
            "items": {
              "type": "object",
              "properties": {
                "title": { "type": "string" },
                "snippet": { "type": "string" },
                "url": { "type": "string" }
              }
            }
          }
        }
      },
      "rate_limit": 30,
      "timeout_ms": 10000,
      "version": "1.0.0",
      "created_at": "2024-01-01T00:00:00.000Z"
    },
    {
      "id": "990e8400-e29b-41d4-a716-446655440002",
      "user_id": "550e8400-e29b-41d4-a716-446655440000",
      "name": "weather_api",
      "description": "Get current weather conditions and forecasts for any location.",
      "category": "Data",
      "icon": "Cloud",
      "is_system": false,
      "is_custom": true,
      "status": "active",
      "input_schema": {
        "type": "object",
        "properties": {
          "location": {
            "type": "string",
            "description": "City name or coordinates"
          },
          "units": {
            "type": "string",
            "enum": ["celsius", "fahrenheit"],
            "default": "celsius"
          }
        },
        "required": ["location"]
      },
      "endpoint_url": "https://api.weatherapi.com/v1/current.json",
      "endpoint_method": "GET",
      "authentication": {
        "type": "api_key",
        "key_name": "key",
        "location": "query"
      },
      "rate_limit": 60,
      "timeout_ms": 5000,
      "version": "1.0.0",
      "created_at": "2024-01-15T10:00:00.000Z"
    }
  ],
  "error": null
}

// System tools only
const { data } = await supabase
  .from('tools')
  .select('*')
  .eq('is_system', true);

// By category
const { data } = await supabase
  .from('tools')
  .select('*')
  .eq('category', 'Research');

// Active tools only
const { data } = await supabase
  .from('tools')
  .select('*')
  .eq('status', 'active');

// Custom tools by user
const { data } = await supabase
  .from('tools')
  .select('*')
  .eq('is_custom', true)
  .eq('user_id', userId);

const { data: tool, error } = await supabase
  .from('tools')
  .select(`
    *,
    tool_usage (
      id,
      status,
      execution_time_ms,
      created_at
    )
  `)
  .eq('id', toolId)
  .maybeSingle();

const { data: tool, error } = await supabase
  .from('tools')
  .insert({
    name: 'stock_price',
    description: 'Get real-time stock price and basic information for a ticker symbol.',
    category: 'Finance',
    icon: 'TrendingUp',
    input_schema: {
      type: 'object',
      properties: {
        symbol: {
          type: 'string',
          description: 'Stock ticker symbol (e.g., AAPL, GOOGL)'
        }
      },
      required: ['symbol']
    },
    output_schema: {
      type: 'object',
      properties: {
        symbol: { type: 'string' },
        price: { type: 'number' },
        change: { type: 'number' },
        change_percent: { type: 'number' },
        volume: { type: 'integer' },
        last_updated: { type: 'string' }
      }
    },
    endpoint_url: 'https://api.example.com/stocks/quote',
    endpoint_method: 'GET',
    authentication: {
      type: 'api_key',
      key_name: 'X-API-Key',
      location: 'header'
    },
    rate_limit: 100,
    timeout_ms: 5000
  })
  .select()
  .single();

// API Key in header
authentication: {
  type: 'api_key',
  key_name: 'X-API-Key',
  location: 'header'
}

// API Key in query
authentication: {
  type: 'api_key',
  key_name: 'apikey',
  location: 'query'
}

// Bearer token
authentication: {
  type: 'bearer',
  token_field: 'access_token'
}

// Basic auth
authentication: {
  type: 'basic',
  username_field: 'api_user',
  password_field: 'api_pass'
}

// OAuth2
authentication: {
  type: 'oauth2',
  client_id_field: 'client_id',
  client_secret_field: 'client_secret',
  token_url: 'https://api.example.com/oauth/token'
}

const { data: tool, error } = await supabase
  .from('tools')
  .update({
    description: 'Updated description...',
    rate_limit: 50,
    status: 'active'
  })
  .eq('id', toolId)
  .eq('user_id', userId) // Can only update own tools
  .select()
  .single();

const { error } = await supabase
  .from('tools')
  .delete()
  .eq('id', toolId)
  .eq('is_custom', true); // Cannot delete system tools

const response = await fetch(
  `${process.env.NEXT_PUBLIC_SUPABASE_URL}/functions/v1/execute-tool`,
  {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json',
      'apikey': process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
    },
    body: JSON.stringify({
      tool_id: '990e8400-e29b-41d4-a716-446655440002',
      input: {
        location: 'San Francisco, CA',
        units: 'fahrenheit'
      },
      agent_id: agentId,      // Optional
      swarm_id: swarmId,      // Optional
      timeout_ms: 10000       // Optional override
    })
  }
);

const result = await response.json();

{
  "success": true,
  "tool_id": "990e8400-e29b-41d4-a716-446655440002",
  "tool_name": "weather_api",
  "output": {
    "location": "San Francisco, CA",
    "temperature": 65,
    "units": "fahrenheit",
    "conditions": "Partly Cloudy",
    "humidity": 72,
    "wind_mph": 12,
    "last_updated": "2024-01-15T14:30:00Z"
  },
  "execution_time_ms": 234,
  "usage_id": "aa0e8400-e29b-41d4-a716-446655440001"
}

{
  "success": false,
  "error": {
    "code": "TOOL_TIMEOUT",
    "message": "Tool execution timed out after 5000ms",
    "tool_id": "990e8400-e29b-41d4-a716-446655440002"
  },
  "execution_time_ms": 5001
}

const response = await fetch(
  `${SUPABASE_URL}/functions/v1/generate-tool`,
  {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      description: 'A tool that fetches cryptocurrency prices from CoinGecko',
      example_input: { symbol: 'bitcoin' },
      example_output: { price_usd: 42000, change_24h: 2.5 }
    })
  }
);

const { tool_definition } = await response.json();

const { data: userTools, error } = await supabase
  .from('user_tools')
  .select(`
    id,
    tool_id,
    is_enabled,
    configuration,
    created_at,
    tool:tools (
      id,
      name,
      description,
      category,
      icon,
      is_system,
      input_schema
    )
  `)
  .eq('user_id', userId);

const { data, error } = await supabase
  .from('user_tools')
  .upsert({
    user_id: userId,
    tool_id: toolId,
    is_enabled: true,
    configuration: {
      api_key: 'your-api-key-here',
      default_units: 'celsius',
      max_results: 5
    }
  })
  .select()
  .single();

const { error } = await supabase
  .from('user_tools')
  .update({ is_enabled: false })
  .eq('user_id', userId)
  .eq('tool_id', toolId);

const { data: agentTools, error } = await supabase
  .from('agent_tools')
  .select(`
    id,
    enabled,
    configuration,
    tool:tools (*)
  `)
  .eq('agent_id', agentId);

const { data, error } = await supabase
  .from('agent_tools')
  .insert({
    agent_id: agentId,
    tool_id: toolId,
    enabled: true,
    configuration: {
      override_defaults: true,
      max_calls_per_message: 3
    }
  })
  .select()
  .single();

const { data: usage, error } = await supabase
  .from('tool_usage')
  .select(`
    id,
    tool_id,
    agent_id,
    status,
    execution_time_ms,
    created_at,
    tool:tools (name, category)
  `)
  .eq('user_id', userId)
  .order('created_at', { ascending: false })
  .limit(100);

// Get aggregated stats (use RPC function)
const { data: stats, error } = await supabase
  .rpc('get_tool_usage_stats', {
    p_user_id: userId,
    p_days: 30
  });

// Returns:
// {
//   total_executions: 1234,
//   successful: 1180,
//   failed: 54,
//   avg_execution_time_ms: 456,
//   by_tool: [
//     { tool_name: 'web_search', count: 500 },
//     { tool_name: 'weather_api', count: 300 }
//   ]
// }