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

!include shared_config.yaml

apps:
  - name: database_agent_app
    app_module: solace_agent_mesh.agent.sac.app
    
    broker:
      <<: *broker_connection
    
    app_config:
      namespace: ${NAMESPACE}
      agent_name: "DatabaseAgent"
      display_name: "SQL Database Agent"
      supports_streaming: false
      model: *general_model
      
      instruction: |
        You are an expert SQL assistant for the company database.
        
        Your capabilities:
        - Translate natural language questions into accurate SQL queries
        - Execute queries and return results
        - Handle errors by analyzing and correcting SQL
        - Save large result sets as artifacts
        
        Query workflow:
        1. Understand the user's question
        2. Review the database schema and example queries
        3. Generate appropriate SQL query
        4. Call 'execute_sql_query' tool
        5. If error occurs, analyze and retry with corrected SQL
        6. Format results clearly for the user
        7. If results are saved as artifact, inform the user
        
        Always use the 'execute_sql_query' tool to interact with the database.
        Never make assumptions about table structure - refer to the schema.
      
      # Agent initialization with database setup
      agent_init_function:
        module: "sam_sql_database.lifecycle"
        name: "initialize_sql_agent"
        config:
          db_type: "${DATABASE_DB_TYPE}"
          db_name: "${DATABASE_DB_NAME}"
          database_purpose: "${DATABASE_DB_PURPOSE}"
          data_description: "${DATABASE_DB_DESCRIPTION}"
          
          # Auto-detect database schema
          auto_detect_schema: true
          
          # Import CSV data on startup
          csv_directories:
            - "data/imports"
          
          # Example queries to guide the LLM
          query_examples:
            - natural_language: "Show all customers from New York"
              sql_query: "SELECT * FROM customers WHERE city = 'New York';"
            
            - natural_language: "What are the top 5 best-selling products?"
              sql_query: |
                SELECT product_name, SUM(quantity) as total_sold 
                FROM order_items 
                JOIN products ON order_items.product_id = products.id 
                GROUP BY product_name 
                ORDER BY total_sold DESC 
                LIMIT 5;
            
            - natural_language: "Calculate total revenue by month"
              sql_query: |
                SELECT 
                  strftime('%Y-%m', order_date) as month,
                  SUM(total_amount) as revenue
                FROM orders
                GROUP BY month
                ORDER BY month DESC;
      
      agent_cleanup_function:
        module: "sam_sql_database.lifecycle"
        name: "cleanup_sql_agent_resources"
      
      # SQL query tool
      tools:
        - tool_type: python
          component_module: "sam_sql_database.tools"
          function_name: "execute_sql_query"
      
      session_service:
        <<: *default_session_service
      
      artifact_service:
        <<: *default_artifact_service
      
      # Agent card for discovery
      agent_card:
        description: |
          SQL Database Agent with access to company data including customers,
          orders, products, inventory, and sales history. Can answer questions
          about business metrics, customer analytics, and operational data.
        
        defaultInputModes: ["text"]
        defaultOutputModes: ["text", "file"]
        
        skills:
          - id: "sql_query"
            name: "Database Query"
            description: "Query company database for customers, orders, products, and sales data"
            examples:
              - "How many orders were placed last month?"
              - "Show me the top customers by revenue"
              - "What products are low in stock?"
              - "Generate a sales report for Q4"
            tags: ["database", "sql", "analytics"]
          
          - id: "data_analysis"
            name: "Data Analysis"
            description: "Analyze trends and patterns in business data"
            examples:
              - "What is the average order value?"
              - "Show sales trends over the last 6 months"
              - "Compare product performance by category"
            tags: ["analytics", "reporting"]
      
      agent_card_publishing: { interval_seconds: 30 }
      agent_discovery: { enabled: true }
      
      inter_agent_communication:
        allow_list: ["*"]
        request_timeout_seconds: 60

id,name,email,city,signup_date
1,John Doe,[email protected],New York,2024-01-15
2,Jane Smith,[email protected],Los Angeles,2024-02-20
3,Bob Johnson,[email protected],Chicago,2024-03-10

-- Create read-only user
CREATE USER sam_agent WITH PASSWORD 'secure_password';

-- Grant read access
GRANT CONNECT ON DATABASE mydb TO sam_agent;
GRANT USAGE ON SCHEMA public TO sam_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sam_agent;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO sam_agent;

-- Future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO sam_agent;

When users ask for data analysis:
1. Use DatabaseAgent to query the data
2. Use DataVisualizationAgent to create charts
3. Use ReportingAgent to generate formatted reports
4. Return the complete analysis to the user