#!/bin/bash

echo "=== ARGUS DATABASE AUDIT ==="
echo

# Check if sqlite3 is available
if ! command -v sqlite3 &> /dev/null; then
    echo "sqlite3 command not found"
    exit 1
fi

# Define database paths to check
declare -a DB_PATHS=(
    "/opt/struktur/social-media-agent/cases.db"
    "/opt/struktur/social-media-agent/db/posts.db"
    "/opt/struktur/social-media-radar/signals/radar.db"
    "/var/lib/sma-data/signals.db"
    "/var/lib/sma-data/cases.db"
    "/opt/struktur/knowledge.db"
)

echo "Checking database accessibility:"
echo "------------------------------"

for db_path in "${DB_PATHS[@]}"; do
    if [[ -f "$db_path" ]]; then
        # Check if it's actually a SQLite database
        if sqlite3 "$db_path" "SELECT 1;" 2>/dev/null; then
            # Get table count
            table_count=$(sqlite3 "$db_path" "SELECT COUNT(*) FROM sqlite_master WHERE type='table';" 2>/dev/null)
            echo "✓ $db_path: SQLite DB with $table_count tables"
            
            # If it's a real DB, get more info
            if [[ "$table_count" -gt 0 ]]; then
                # Get table names
                tables=$(sqlite3 "$db_path" "SELECT name FROM sqlite_master WHERE type='table';" 2>/dev/null | tr '\n' ', ' | sed 's/, $//')
                echo "  Tables: $tables"
                
                # Check integrity
                integrity=$(sqlite3 "$db_path" "PRAGMA integrity_check;" 2>/dev/null)
                echo "  Integrity: $integrity"
                
                # Check foreign keys
                fk_output=$(sqlite3 "$db_path" "PRAGMA foreign_key_check;" 2>/dev/null)
                fk_count=$(echo "$fk_output" | wc -l)
                echo "  FK issues: $fk_count"
                if [[ "$fk_count" -gt 0 ]]; then
                    echo "  FK Details:"
                    echo "$fk_output" | head -3
                fi
            fi
        else
            echo "✗ $db_path: File exists but not a valid SQLite database"
        fi
    else
        echo "? $db_path: File not found"
    fi
    echo
done

echo "Checking for direct SQL access in codebase:"
echo "------------------------------------------"

# Count sqlite3.connect occurrences
if [[ -d "/opt/struktur" ]]; then
    sql_count=$(grep -r "sqlite3\.connect" /opt/struktur --include="*.py" 2>/dev/null | wc -l)
    echo "Found $sql_count occurrences of sqlite3.connect in Python files"
    
    if [[ $sql_count -gt 0 ]]; then
        echo "Locations (first 5):"
        grep -r "sqlite3\.connect" /opt/struktur --include="*.py" 2>/dev/null | head -5
    fi
else
    echo "/opt/struktur directory not found"
fi

echo
echo "Checking environment variables for DB paths:"
echo "------------------------------------------"
env | grep -i "SMA_\|DATABASE\|DB_" || echo "No SMA/DATABASE related env vars found"

echo
echo "Audit complete."