SQL Database Integration

import sqlite3
from datetime import datetime, timedelta
import random

def create_database():
    # Connect to database (creates it if it doesn't exist)
    conn = sqlite3.connect('coffee_shop.db')
    cursor = conn.cursor()
    
    # Create tables
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        category TEXT NOT NULL,
        price REAL NOT NULL
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        loyalty_points INTEGER DEFAULT 0
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        customer_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        order_date TEXT NOT NULL,
        total_amount REAL NOT NULL,
        FOREIGN KEY (customer_id) REFERENCES customers(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    )
    ''')
    
    # Insert sample products
    products = [
        ('Espresso', 'Coffee', 3.50),
        ('Cappuccino', 'Coffee', 4.50),
        ('Latte', 'Coffee', 4.75),
        ('Americano', 'Coffee', 3.75),
        ('Mocha', 'Coffee', 5.25),
        ('Croissant', 'Pastry', 3.25),
        ('Muffin', 'Pastry', 3.50),
        ('Bagel', 'Pastry', 2.75),
        ('Green Tea', 'Tea', 3.00),
        ('Chai Latte', 'Tea', 4.25),
    ]
    
    cursor.executemany(
        'INSERT INTO products (name, category, price) VALUES (?, ?, ?)',
        products
    )
    
    # Insert sample customers
    customers = [
        ('Alice Johnson', '[email protected]', 150),
        ('Bob Smith', '[email protected]', 230),
        ('Carol White', '[email protected]', 95),
        ('David Brown', '[email protected]', 180),
        ('Eve Davis', '[email protected]', 320),
    ]
    
    cursor.executemany(
        'INSERT INTO customers (name, email, loyalty_points) VALUES (?, ?, ?)',
        customers
    )
    
    # Generate sample orders for the past 30 days
    start_date = datetime.now() - timedelta(days=30)
    
    for _ in range(100):
        customer_id = random.randint(1, 5)
        product_id = random.randint(1, 10)
        quantity = random.randint(1, 3)
        
        # Get product price
        cursor.execute('SELECT price FROM products WHERE id = ?', (product_id,))
        price = cursor.fetchone()[0]
        total = price * quantity
        
        # Random date in the past 30 days
        days_ago = random.randint(0, 30)
        order_date = (start_date + timedelta(days=days_ago)).strftime('%Y-%m-%d')
        
        cursor.execute(
            '''INSERT INTO orders 
               (customer_id, product_id, quantity, order_date, total_amount) 
               VALUES (?, ?, ?, ?, ?)''',
            (customer_id, product_id, quantity, order_date, total)
        )
    
    conn.commit()
    conn.close()
    
    print("✓ Database created successfully!")
    print("✓ Sample data inserted")
    print("\nDatabase file: coffee_shop.db")

if __name__ == '__main__':
    create_database()

log:
  stdout_log_level: INFO
  log_file_level: DEBUG
  log_file: database_agent.log

!include shared_config.yaml

apps:
  - name: database_analyst_app
    app_base_path: .
    app_module: solace_agent_mesh.agent.sac.app
    broker:
      <<: *broker_connection

    app_config:
      namespace: ${NAMESPACE}
      agent_name: "DatabaseAnalyst"
      display_name: "Coffee Shop Data Analyst"
      model: *planning_model
      
      instruction: |
        You are a data analyst for a coffee shop. You have access to a SQL
        database with information about products, customers, and orders.
        
        When asked questions about the business:
        1. Write a SQL query to get the needed information
        2. Execute the query using the sql_query tool
        3. Analyze the results
        4. Provide insights in a business-friendly format
        
        Database schema:
        
        **products** table:
        - id: INTEGER (primary key)
        - name: TEXT (product name)
        - category: TEXT (Coffee, Pastry, Tea)
        - price: REAL (price in dollars)
        
        **customers** table:
        - id: INTEGER (primary key)
        - name: TEXT (customer name)
        - email: TEXT (email address)
        - loyalty_points: INTEGER (reward points)
        
        **orders** table:
        - id: INTEGER (primary key)
        - customer_id: INTEGER (foreign key to customers)
        - product_id: INTEGER (foreign key to products)
        - quantity: INTEGER (number of items)
        - order_date: TEXT (YYYY-MM-DD format)
        - total_amount: REAL (total cost)
        
        Always explain your queries and findings clearly. When showing
        data, format it as tables or charts when appropriate.
      
      # Enable SQL query tools
      tools:
        - tool_type: builtin-group
          group_name: "data_analysis"
        - tool_type: builtin-group
          group_name: "artifact_management"
      
      # Data analysis configuration
      data_tools_config:
        sqlite_memory_threshold_mb: 100
        max_result_preview_rows: 50
      
      supports_streaming: true
      
      session_service:
        type: "memory"
        default_behavior: "PERSISTENT"
      
      artifact_service:
        type: "filesystem"
        base_path: "/tmp/samv2"
        artifact_scope: namespace
      
      artifact_handling_mode: "reference"
      enable_embed_resolution: true
      
      agent_card:
        description: |
          Analyzes coffee shop business data using SQL queries.
          Can answer questions about sales, customers, and products.
        defaultInputModes: ["text"]
        defaultOutputModes: ["text", "file"]
        skills:
          - id: "sales_analysis"
            name: "Sales Analysis"
            description: "Analyzes sales data and revenue"
            examples:
              - "What are our top-selling products?"
              - "What's our total revenue this month?"
            tags: ["analytics", "sql"]
          - id: "customer_insights"
            name: "Customer Insights"
            description: "Provides customer analytics"
            examples:
              - "Who are our best customers?"
              - "How many customers do we have?"
            tags: ["analytics", "crm"]
      
      agent_card_publishing: { interval_seconds: 10 }
      agent_discovery: { enabled: true }

# Existing configuration...

# Database settings
DATABASE_URL=sqlite:///coffee_shop.db