#!/bin/bash
# Verify database state - check that tables have expected data
# Usage: ./verify_database_state.sh [db_path]
# Default: ../db/test.duckdb

DB_PATH="${1:-../db/test.duckdb}"

echo "=== Database State Verification ==="
echo "Database: $DB_PATH"
echo ""

{
  # Initialize MCP connection
  echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"verify","version":"1.0"}}}'
  sleep 0.2

  # Check datasets
  echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as dataset_count FROM dataset WHERE active = true"}}}'
  sleep 0.2

  # Check locations
  echo '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as location_count FROM location WHERE active = true"}}}'
  sleep 0.2

  # Check clusters
  echo '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cluster_count FROM cluster WHERE active = true"}}}'
  sleep 0.2

  # Check files
  echo '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as file_count FROM file WHERE active = true"}}}'
  sleep 0.2

  # Check file_dataset junction table
  echo '{"jsonrpc":"2.0","id":6,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as file_dataset_count FROM file_dataset"}}}'
  sleep 0.2

  # Check moth_metadata
  echo '{"jsonrpc":"2.0","id":7,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as moth_metadata_count FROM moth_metadata WHERE active = true"}}}'
  sleep 0.2

  # Check for orphaned files (files without file_dataset entries - SHOULD BE ZERO)
  echo '{"jsonrpc":"2.0","id":8,"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as orphaned_files FROM file f LEFT JOIN file_dataset fd ON f.id = fd.file_id WHERE f.active = true AND fd.file_id IS NULL"}}}'
  sleep 0.2

} | ../skraak_mcp "$DB_PATH" 2>/dev/null | jq -s '
  . as $responses |
  {
    "datasets": ($responses[] | select(.id == 2) | .result.content[0].text | fromjson | .rows[0][0]),
    "locations": ($responses[] | select(.id == 3) | .result.content[0].text | fromjson | .rows[0][0]),
    "clusters": ($responses[] | select(.id == 4) | .result.content[0].text | fromjson | .rows[0][0]),
    "files": ($responses[] | select(.id == 5) | .result.content[0].text | fromjson | .rows[0][0]),
    "file_dataset_entries": ($responses[] | select(.id == 6) | .result.content[0].text | fromjson | .rows[0][0]),
    "moth_metadata_entries": ($responses[] | select(.id == 7) | .result.content[0].text | fromjson | .rows[0][0]),
    "orphaned_files": ($responses[] | select(.id == 8) | .result.content[0].text | fromjson | .rows[0][0])
  }
'

echo ""
echo "=== Verification ==="
echo "✓ All queries completed successfully"
echo "⚠️  Check that orphaned_files = 0 (files should have file_dataset entries)"
echo "⚠️  Check that file_dataset_entries ≥ files (should be equal or more if multiple datasets per file)"