tools:
  - type: mcp
    name: database
    config:
      connection_string: ${DATABASE_URL}
      database_type: postgresql
      allowed_operations: ["SELECT", "WITH"]
      
      # Query limits
      max_rows: 1000
      timeout: 30  # seconds
      
      # Schema restrictions
      allowed_schemas: ["public", "analytics"]
      allowed_tables: ["sales", "customers", "products"]
      
      # Security
      enable_explain: true
      log_queries: true

SELECT 
    p.product_name,
    SUM(s.quantity * s.unit_price) as revenue,
    COUNT(DISTINCT s.order_id) as order_count
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.order_date >= '2024-01-01' 
  AND s.order_date < '2024-04-01'
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 10;

WITH monthly_cohorts AS (
    SELECT 
        DATE_TRUNC('month', first_purchase) as cohort_month,
        customer_id,
        DATE_TRUNC('month', last_purchase) as last_active
    FROM (
        SELECT 
            customer_id,
            MIN(order_date) as first_purchase,
            MAX(order_date) as last_purchase
        FROM orders
        GROUP BY customer_id
    ) customer_activity
)
SELECT 
    cohort_month,
    COUNT(DISTINCT customer_id) as total_customers,
    COUNT(DISTINCT CASE 
        WHEN last_active < cohort_month + INTERVAL '3 months' 
        THEN customer_id 
    END) as churned_customers,
    ROUND(100.0 * COUNT(DISTINCT CASE 
        WHEN last_active < cohort_month + INTERVAL '3 months' 
        THEN customer_id 
    END) / COUNT(DISTINCT customer_id), 2) as churn_rate
FROM monthly_cohorts
GROUP BY cohort_month
ORDER BY cohort_month;

-- Check for nulls in critical fields
SELECT 
    COUNT(*) as total_rows,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) as null_customers,
    SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) as null_dates,
    SUM(CASE WHEN total_amount IS NULL THEN 1 ELSE 0 END) as null_amounts
FROM orders;

-- Check for duplicates
SELECT order_id, COUNT(*) as duplicate_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Check for invalid values
SELECT COUNT(*) as negative_amounts
FROM orders
WHERE total_amount < 0;

-- Create a read-only user
CREATE USER mcp_reader WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;

-- Pre-aggregate common queries
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE(order_date) as sale_date,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value
FROM orders
GROUP BY DATE(order_date);

-- Refresh periodically
REFRESH MATERIALIZED VIEW daily_sales_summary;

config:
  log_queries: true
  log_destination: "audit_log"
  include_results: false  # Don't log sensitive data

config:
  timeout: 30  # Kill queries after 30 seconds
  statement_timeout: "30s"  # PostgreSQL specific

Assistant: "Sales Performance Analyst"
Tools: Database + Charts
Use: Real-time sales metrics and visualizations

Assistant: "Support Agent Helper"
Tools: Database + Memory
Use: Look up customer information and history

Assistant: "Inventory Analyst"
Tools: Database + Alerts
Use: Monitor stock levels and predict shortages

Assistant: "Financial Reporter"
Tools: Database + Document Generation
Use: Create automated financial reports

config:
  templates:
    daily_revenue: |
      SELECT DATE(order_date) as date, 
             SUM(total_amount) as revenue
      FROM orders
      WHERE order_date >= :start_date
      GROUP BY DATE(order_date)

config:
  cache:
    enabled: true
    ttl: 300  # 5 minutes
    max_size: 100  # queries

tools:
  - type: mcp
    name: database
    alias: "sales_db"
    config:
      connection_string: ${SALES_DB_URL}
      
  - type: mcp
    name: database  
    alias: "analytics_db"
    config:
      connection_string: ${ANALYTICS_DB_URL}