{
  "tool_type": "EXTERNAL_DATASOURCE",
  "name": "Company Data Warehouse",
  "description": "Query company analytics data using natural language",
  "config": {
    "external_datasource_type": "BIG_QUERY",
    "big_query_config": {
      "service_account_credentials": {
        "type": "service_account",
        "project_id": "my-gcp-project-id",
        "private_key_id": "abc123def456...",
        "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQ...\n-----END PRIVATE KEY-----\n",
        "client_email": "plai-bigquery@my-gcp-project.iam.gserviceaccount.com",
        "client_id": "123456789012345678901",
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://oauth2.googleapis.com/token",
        "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
        "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/plai-bigquery%40my-gcp-project.iam.gserviceaccount.com"
      },
      "dataset_id": "analytics_prod",
      "table_ids": ["customers", "orders", "products", "transactions"],
      "system_prompt": null,
      "query_max_rows": 10000,
      "output_max_rows": 10
    }
  }
}

-- Select with filtering
SELECT 
  customer_id,
  name,
  email,
  signup_date
FROM `project-id.analytics_prod.customers`
WHERE DATE(signup_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY signup_date DESC;

-- Revenue by product category
SELECT 
  p.category,
  COUNT(DISTINCT o.order_id) as order_count,
  SUM(oi.quantity) as total_units_sold,
  SUM(oi.quantity * oi.price) as total_revenue,
  AVG(oi.price) as avg_price
FROM `project-id.analytics_prod.products` AS p
JOIN `project-id.analytics_prod.order_items` AS oi
  ON p.product_id = oi.product_id
JOIN `project-id.analytics_prod.orders` AS o
  ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Daily active users trend
SELECT 
  DATE(event_timestamp) as date,
  COUNT(DISTINCT user_id) as daily_active_users,
  COUNT(*) as total_events,
  COUNT(*) / COUNT(DISTINCT user_id) as avg_events_per_user
FROM `project-id.analytics_prod.user_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date;

-- Sales ranking with running totals
SELECT 
  sales_rep,
  sale_date,
  sale_amount,
  ROW_NUMBER() OVER (
    PARTITION BY sales_rep 
    ORDER BY sale_amount DESC
  ) as rank_within_rep,
  SUM(sale_amount) OVER (
    PARTITION BY sales_rep 
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM `project-id.analytics_prod.sales`
WHERE sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

{
  sql_query: string,           // Generated BigQuery SQL
  prompt_tokens: number,        // Tokens used for GPT-4 request
  completion_tokens: number,    // Tokens used in GPT-4 response
  json_table: {
    columns: string[],          // Column names
    rows: any[][]               // Data rows (up to output_max_rows)
  } | null
}

{
  "sql_query": "SELECT customer_id, name, email, SUM(total_amount) as revenue FROM `project.dataset.orders` JOIN `project.dataset.customers` USING(customer_id) WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY customer_id, name, email ORDER BY revenue DESC LIMIT 10",
  "prompt_tokens": 1247,
  "completion_tokens": 156,
  "json_table": {
    "columns": ["customer_id", "name", "email", "revenue"],
    "rows": [
      ["CUST-001", "Acme Corp", "contact@acme.com", 125000.50],
      ["CUST-002", "TechStart Inc", "hello@techstart.io", 98750.25],
      ["CUST-003", "Global Industries", "info@global.com", 87500.00]
    ]
  }
}

Agent Query: "Show me the last 5 orders for customer john@example.com"

Generated SQL:
SELECT order_id, order_date, total_amount, status
FROM `project.dataset.orders`
WHERE customer_email = 'john@example.com'
ORDER BY order_date DESC
LIMIT 5;

Agent Query: "What was our total revenue by product category last quarter?"

Generated SQL:
SELECT 
  p.category,
  SUM(oi.quantity * oi.price) as total_revenue
FROM `project.dataset.order_items` oi
JOIN `project.dataset.products` p ON oi.product_id = p.product_id
JOIN `project.dataset.orders` o ON oi.order_id = o.order_id
WHERE DATE_TRUNC(o.order_date, QUARTER) = 
      DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 QUARTER), QUARTER)
GROUP BY p.category
ORDER BY total_revenue DESC;

Agent Query: "Show me any failed transactions in the last hour"

Generated SQL:
SELECT 
  transaction_id,
  customer_id,
  amount,
  error_message,
  timestamp
FROM `project.dataset.transactions`
WHERE status = 'FAILED'
  AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
ORDER BY timestamp DESC;

Agent Query: "Which sales reps exceeded their quota this month?"

Generated SQL:
WITH monthly_sales AS (
  SELECT 
    sales_rep_id,
    SUM(sale_amount) as total_sales
  FROM `project.dataset.sales`
  WHERE DATE_TRUNC(sale_date, MONTH) = DATE_TRUNC(CURRENT_DATE(), MONTH)
  GROUP BY sales_rep_id
)
SELECT 
  sr.name,
  sr.quota,
  ms.total_sales,
  ms.total_sales - sr.quota as over_quota
FROM monthly_sales ms
JOIN `project.dataset.sales_reps` sr ON ms.sales_rep_id = sr.sales_rep_id
WHERE ms.total_sales > sr.quota
ORDER BY over_quota DESC;