# Test direct database access

$ErrorActionPreference = "Continue"

Write-Host "========================================" -ForegroundColor Cyan
Write-Host "Testing Direct Database Access" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host ""

$dbPath = "C:\GEVISOFT\DATABASE\GeViDB.mdb"
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$dbPath;Mode=Read;"

try {
    Write-Host "Connecting to database..." -ForegroundColor Yellow
    $conn = New-Object -ComObject ADODB.Connection
    $conn.Open($connectionString)
    Write-Host "  [OK] Connected" -ForegroundColor Green
    Write-Host ""

    # List all tables
    Write-Host "Listing tables..." -ForegroundColor Yellow
    $schema = $conn.OpenSchema(20) # adSchemaTables = 20
    $tables = @()
    while (-not $schema.EOF) {
        $tableName = $schema.Fields.Item("TABLE_NAME").Value
        if (-not $tableName.StartsWith("MSys")) {
            $tables += $tableName
        }
        $schema.MoveNext()
    }
    $schema.Close()

    Write-Host "  Found $($tables.Count) user tables:" -ForegroundColor Gray
    foreach ($table in $tables | Sort-Object) {
        Write-Host "    - $table" -ForegroundColor White
    }
    Write-Host ""

    # Check if Alarms table exists
    if ($tables -contains "Alarms") {
        Write-Host "Querying Alarms table..." -ForegroundColor Yellow
        $rs = $conn.Execute("SELECT COUNT(*) FROM Alarms")
        $count = $rs.Fields.Item(0).Value
        $rs.Close()
        Write-Host "  [OK] Alarms table has $count records" -ForegroundColor Green
        Write-Host ""

        # Get sample records
        Write-Host "Sample alarm records:" -ForegroundColor Yellow
        $rs = $conn.Execute("SELECT TOP 5 * FROM Alarms")
        $fieldCount = $rs.Fields.Count
        Write-Host "  Fields: $fieldCount" -ForegroundColor Gray

        for ($i = 0; $i -lt $fieldCount; $i++) {
            Write-Host "    $($i): $($rs.Fields.Item($i).Name) ($($rs.Fields.Item($i).Type))" -ForegroundColor Gray
        }
        $rs.Close()
    } else {
        Write-Host "[WARN] Alarms table not found" -ForegroundColor Yellow
    }

    # Check if Actions table exists
    if ($tables -contains "Actions") {
        Write-Host ""
        Write-Host "Querying Actions table..." -ForegroundColor Yellow
        $rs = $conn.Execute("SELECT COUNT(*) FROM Actions")
        $count = $rs.Fields.Item(0).Value
        $rs.Close()
        Write-Host "  [OK] Actions table has $count records" -ForegroundColor Green
    } else {
        Write-Host ""
        Write-Host "[WARN] Actions table not found" -ForegroundColor Yellow
        Write-Host "  The database schema might be different than expected" -ForegroundColor Gray
    }

    $conn.Close()

} catch {
    Write-Host ""
    Write-Host "[ERROR] $($_.Exception.Message)" -ForegroundColor Red
}

Write-Host ""
Write-Host "========================================" -ForegroundColor Cyan