#!/bin/bash
# Verify database state - check table counts and data integrity
# Usage: ./test_db_state.sh [db_path]
# Default: uses test.duckdb (read-only)

source "$(dirname "$0")/test_lib.sh"

DB_PATH="${1:-$DEFAULT_TEST_DB}"

if [ ! -f "$DB_PATH" ]; then
    echo -e "${RED}Error: Database not found at $DB_PATH${NC}"
    exit 1
fi

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

check_binary

# Run multiple queries in single session
results=$(send_requests "$DB_PATH" \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM dataset WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM location WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM cluster WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM file WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM file_dataset"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM moth_metadata WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as orphaned FROM file f LEFT JOIN file_dataset fd ON f.id = fd.file_id WHERE f.active = true AND fd.file_id IS NULL"}}' \
)

# Extract counts
datasets=$(echo "$results" | grep '"id":2' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
locations=$(echo "$results" | grep '"id":3' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
clusters=$(echo "$results" | grep '"id":4' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
files=$(echo "$results" | grep '"id":5' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
file_dataset=$(echo "$results" | grep '"id":6' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
moth_metadata=$(echo "$results" | grep '"id":7' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
orphaned=$(echo "$results" | grep '"id":8' | head -1 | jq -r '.result.structuredContent.rows[0].orphaned // "error"')

# Print results
echo "Table Counts:"
echo "  Datasets:       $datasets"
echo "  Locations:      $locations"
echo "  Clusters:       $clusters"
echo "  Files:          $files"
echo "  File-Dataset:   $file_dataset"
echo "  Moth Metadata:  $moth_metadata"
echo ""

# Check integrity
echo "Integrity Checks:"

if [ "$orphaned" = "0" ]; then
    echo -e "  ${GREEN}✓${NC} No orphaned files (files have file_dataset entries)"
    ((TESTS_PASSED++)) || true
else
    echo -e "  ${RED}✗${NC} Found $orphaned orphaned files!"
    ((TESTS_FAILED++)) || true
fi

if [ "$files" = "$file_dataset" ] || [ "$file_dataset" -ge "$files" ]; then
    echo -e "  ${GREEN}✓${NC} file_dataset count >= file count"
    ((TESTS_PASSED++)) || true
else
    echo -e "  ${RED}✗${NC} file_dataset count < file count (missing entries)"
    ((TESTS_FAILED++)) || true
fi

((TESTS_RUN+=2)) || true

echo ""
echo "Summary: $TESTS_PASSED/$TESTS_RUN checks passed"

if [ "$TESTS_FAILED" -gt 0 ]; then
    exit 1
fi