# TASK 6.3: Test Against Real KDSDev with Sample Data

## Summary

This task covers manual testing and validation of the anomaly detection system against the real KDSDev database. It includes test procedures, expected results, and troubleshooting guidance. All CLI commands use the new `--name`/`--spec-id` syntax with `MonitorState` management.

## Dependencies

- All previous tasks completed
- Access to KDSDev database
- benborla/mcp-server-mysql configured
- AWS Bedrock credentials configured

## Prerequisites Checklist

Before testing, verify:

- [ ] KDSDev database is accessible
- [ ] MCP server can connect to KDSDev (stdio transport configured)
- [ ] AWS Bedrock credentials are configured
- [ ] Application database (kdsaidb) is migrated
- [ ] At least one store has order data in KDSDev

## Test Procedures

### Test 1: Basic Connectivity

**Objective:** Verify all components can connect

```bash
# Step 1: Test database migration (auto-applies at AppHost startup)
# Just start the application and verify no migration errors

# Step 2: List monitors (should show empty or existing)
dotnet run --project backend/Kds.Ai.Cli -- anomaly list
```

**Expected Result:**
- Application starts without migration errors
- List command runs without connection errors
- Output shows "No monitor specs found." or existing monitors

### Test 2: Monitor Spec Creation

**Objective:** Create a monitor spec using the example prompt

```bash
# Create a monitor spec with the built-in example prompt
dotnet run --project backend/Kds.Ai.Cli -- anomaly create \
  -n "Prep Time Monitor - Test Store" \
  --example

# Verify creation
dotnet run --project backend/Kds.Ai.Cli -- anomaly list

# Check it was created in Draft state
dotnet run --project backend/Kds.Ai.Cli -- anomaly list --state draft
```

**Expected Result:**
- Monitor spec created successfully with unique ID
- State shows as `Draft`
- Spec appears in list output

### Test 3: Activate Monitor and Dry Run

**Objective:** Activate the monitor and run analysis without persisting results

```bash
# Activate the monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "YOUR_SPEC_ID" \
  --state active

# Run dry analysis with verbose output
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  --dry-run \
  --verbose
```

**Expected Result:**
- State successfully changed to Active
- Progress messages show each phase
- Agent queries data via MCP mysql_query tool
- Analysis completes with results
- No database records created (dry run)

**Sample Output:**
```
Monitor: Prep Time Monitor - Test Store (abc-123-...)
State: Active
Analysis time: 2025-01-15 14:00:00 UTC

[DRY RUN MODE - No changes will be saved]
[....] LoadingSpec: Loading monitor specification...
[....] Initializing: Initializing monitor run...
[....] RunningAgent: Running anomaly detection agent...
[....] AnalyzingResults: Processing detection results...
[DONE] Completed: No anomalies detected

=== Results ===
Run ID: 00000000-0000-0000-0000-000000000000
Status: Completed
Duration: 4523ms
Anomalies Detected: 0
Tokens Used: 2847
```

### Test 4: Full Run with Alert Emission

**Objective:** Run full analysis and verify alert creation

```bash
# Run without dry-run
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  --verbose
```

**Expected Result:**
- Run completes and is saved to database
- If anomaly detected, alert event(s) created and listed
- Alerts Emitted count shown (from `run.AlertEvents` collection)

### Test 5: Historical Analysis

**Objective:** Analyze a specific historical time period

```bash
# Analyze 2 hours ago
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  -t "-2h" \
  --verbose

# Analyze yesterday
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Prep Time Monitor - Test Store" \
  -t "-1d" \
  --verbose
```

**Expected Result:**
- Analysis uses the specified time window
- Results reflect historical data
- Agent's SQL queries reference appropriate date ranges

### Test 6: Run History and Stats

**Objective:** Verify run history is recorded

```bash
# Check history for the monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly history \
  --spec-id "YOUR_SPEC_ID"

# Check overall stats
dotnet run --project backend/Kds.Ai.Cli -- anomaly stats
```

**Expected Result:**
- History shows all previous runs with status, duration, anomaly counts
- Stats show aggregate information

### Test 7: Batch Execution

**Objective:** Run all active monitors

```bash
# Create a second monitor with example prompt
dotnet run --project backend/Kds.Ai.Cli -- anomaly create \
  -n "Prep Time Monitor - Second Store" \
  --example

# Activate it
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "SECOND_SPEC_ID" \
  --state active

# Run all active monitors
dotnet run --project backend/Kds.Ai.Cli -- anomaly run-all
```

**Expected Result:**
- All monitors in `Active` state execute
- Monitors in `Draft`, `Paused`, or `Archived` states are skipped
- Each run recorded separately
- Summary shows all results

### Test 8: State Management

**Objective:** Verify all state transitions work

```bash
# List all monitors
dotnet run --project backend/Kds.Ai.Cli -- anomaly list --state all

# Pause a monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "YOUR_SPEC_ID" \
  --state paused

# Verify paused monitors don't run
dotnet run --project backend/Kds.Ai.Cli -- anomaly run-all
# Should skip the paused monitor

# Archive a monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly set-state \
  --spec-id "YOUR_SPEC_ID" \
  --state archived

# Verify archived state
dotnet run --project backend/Kds.Ai.Cli -- anomaly list --state archived
```

**Expected Result:**
- State transitions work for all 4 states: Draft, Active, Paused, Archived
- Only Active monitors execute during `run-all`
- List filtering by state works correctly

### Test 9: Custom Prompt File

**Objective:** Create a monitor from a custom prompt file

```bash
# Create a prompt file
cat > /tmp/custom-monitor-prompt.txt << 'EOF'
You are a data quality monitor. Analyze the orders table for data anomalies.

## Database
Use the mysql_query tool to access the MySQL database. The key table is `orders`:
- `create_time` (int, unix timestamp)
- `store_guid` (varchar)
- `is_deleted` (tinyint)
- `done` (tinyint)

## Analysis
1. Check for unusual deletion rates in the last 24 hours
2. Check for stores with zero orders when they normally have activity
3. Call emit_conclusion with your findings
EOF

# Create the monitor
dotnet run --project backend/Kds.Ai.Cli -- anomaly create \
  -n "Data Quality Monitor" \
  -p /tmp/custom-monitor-prompt.txt

# Verify
dotnet run --project backend/Kds.Ai.Cli -- anomaly list
```

**Expected Result:**
- Monitor created with the custom prompt content
- Shows in list with Draft state
- Demonstrates the system is truly generic (not hardcoded to prep times)

### Test 10: Error Handling

**Objective:** Verify graceful error handling

```bash
# Test with nonexistent monitor name
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  -n "Nonexistent Monitor"

# Test with nonexistent spec ID
dotnet run --project backend/Kds.Ai.Cli -- anomaly run \
  --spec-id "00000000-0000-0000-0000-000000000000"

# Test with neither name nor spec-id
dotnet run --project backend/Kds.Ai.Cli -- anomaly run
```

**Expected Result:**
- Nonexistent monitor: Clear error message with suggestion to use `anomaly list` or `anomaly create`
- Missing arguments: Error message about required `--name` or `--spec-id`
- No unhandled exceptions or stack traces in normal mode

## Validation Queries

Run these against KDSDev to understand the data (useful for verifying agent behavior):

```sql
-- Check data freshness
SELECT 
  store_guid,
  MAX(FROM_UNIXTIME(create_time)) as latest_order,
  COUNT(*) as orders_24h
FROM orders
WHERE create_time > UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR)
GROUP BY store_guid
ORDER BY orders_24h DESC
LIMIT 10;

-- Check prep time distribution (example of what the agent might query)
SELECT 
  HOUR(FROM_UNIXTIME(create_time)) as hour,
  AVG(preparation_time) as avg_prep,
  STDDEV(preparation_time) as stddev_prep,
  COUNT(*) as orders
FROM orders
WHERE store_guid = 'YOUR_STORE_GUID'
  AND create_time > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)
  AND done = 1
  AND preparation_time IS NOT NULL
GROUP BY hour
ORDER BY hour;
```

**Note:** These queries are for manual validation only. The agent formulates its own SQL based on the MonitorSpec's system prompt. The queries above are examples of what the agent might run, not hardcoded queries in the system.

## Troubleshooting

### MCP Connection Failed

```
Error: Failed to connect to MCP server
```

**Solutions:**
1. Verify MCP server binary is accessible (`benborla/mcp-server-mysql`)
2. Check `McpMySqlSettings` configuration (connection string, server path)
3. Test MCP server independently: start it manually and send a test query
4. Check stdio transport: ensure the server process can start

### No Data Available

```
Agent found no data or returned empty results
```

**Solutions:**
1. Check if the store referenced in the prompt has recent orders
2. Verify the agent's system prompt references correct table/column names
3. Run a manual query against KDSDev to confirm data exists
4. Check the agent's reasoning/tool calls in verbose output

### LLM Timeout

```
Error: Execution timed out
```

**Solutions:**
1. Increase timeout in RunOptions (default may be too short for complex analysis)
2. Check AWS Bedrock connectivity and credentials
3. Review token usage (may need to simplify the system prompt)
4. Check if the agent is stuck in a tool-calling loop

### SQL Errors from MCP

```
Agent received SQL error from mysql_query tool
```

**Solutions:**
1. Check KDSDev connection string in MCP server config
2. Verify table/column names in the system prompt match actual schema
3. Test the failing query directly in a MySQL client
4. The agent should handle SQL errors gracefully and report them in its analysis

## Performance Benchmarks

Target metrics for a healthy run:

| Metric | Target | Warning |
|--------|--------|---------|
| Total Duration | < 30s | > 60s |
| Token Usage | < 5000 | > 10000 |
| Tool Calls | 3-6 | > 10 |
| Query Time | < 5s each | > 10s |

## Sign-Off Checklist

After completing all tests:

- [ ] Basic connectivity verified
- [ ] Monitor spec creation works (with `--example` and `--prompt-file`)
- [ ] State management works (Draft, Active, Paused, Archived)
- [ ] Dry run completes successfully
- [ ] Full run persists results
- [ ] Alerts emitted when anomalies detected (via `run.AlertEvents` collection)
- [ ] Historical analysis works (relative time: `-2h`, `-1d`)
- [ ] Run history and stats accessible
- [ ] Batch execution runs only Active monitors
- [ ] Custom prompt file creates a non-prep-time monitor (proves generic design)
- [ ] Error handling appropriate (clear messages, no stack traces)
- [ ] Performance within targets
- [ ] No critical bugs found

## Changes from Old Design

- All CLI commands use `--name` / `--spec-id` instead of `--store`
- No `--skip-preflight` option (preflight checks were domain-specific, removed)
- No `--threshold` or `--lookback` options (agent decides these from prompt)
- `anomaly create` uses `--example` or `--prompt-file` instead of `-s "STORE_GUID"`
- `anomaly list` filters by `--state` instead of `-s "STORE_GUID"`
- `anomaly set-state` replaces `anomaly toggle --enable/--disable`
- `anomaly history` filters by `--spec-id` instead of `-s "STORE_GUID"`
- Batch execution via `run-all` calls `ExecuteAllActiveAsync()` instead of creating monitors per store
- Added Test 9 (custom prompt file) to demonstrate the system is generic
- Added Test 8 (state management) for the 4-state MonitorState enum
- Validation queries are framed as examples of what the agent might run, not hardcoded system queries

## Notes

- First runs may be slower due to cold starts (MCP server startup, LLM model loading)
- LLM responses may vary slightly between runs (non-deterministic)
- The agent formulates its own SQL - the queries it runs may differ from the validation queries above
- Document any store-specific issues discovered during testing
- Consider creating a dedicated test store with known patterns for reproducible testing