<?php
// debug-files.php - Check file_risk_analyses table

require_once '../config.php';

echo "<pre>";

try {
    $pdo = getDBConnection();
    $userId = $_SESSION['user_id'] ?? 0;
    
    echo "User ID: $userId\n\n";
    
    // Check if table exists
    $stmt = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='file_risk_analyses'");
    $tableExists = $stmt->fetch();
    
    if (!$tableExists) {
        echo "❌ Table 'file_risk_analyses' does NOT exist!\n";
        echo "You need to create this table first.\n";
    } else {
        echo "✅ Table 'file_risk_analyses' exists\n\n";
        
        // Show table structure
        $stmt = $pdo->query("PRAGMA table_info(file_risk_analyses)");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "Table columns:\n";
        foreach ($columns as $col) {
            echo "  - " . $col['name'] . " (" . $col['type'] . ")\n";
        }
        
        // Count records
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM file_risk_analyses");
        $count = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "\nTotal records in table: " . $count['count'] . "\n";
        
        // Get records for current user
        $stmt = $pdo->prepare("SELECT * FROM file_risk_analyses WHERE user_id = ? ORDER BY created_at DESC");
        $stmt->execute([$userId]);
        $files = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "\nRecords for user_id = $userId: " . count($files) . "\n";
        
        if (!empty($files)) {
            echo "\nRecent files:\n";
            foreach ($files as $file) {
                echo "  - ID: " . $file['id'] . " | File: " . $file['original_filename'] . " | Risk: " . $file['risk_score'] . "% | Status: " . $file['status'] . "\n";
            }
        } else {
            echo "\n❌ No records found for user_id = $userId\n";
            
            // Check if there are records with different user_id
            $stmt = $pdo->query("SELECT DISTINCT user_id FROM file_risk_analyses");
            $users = $stmt->fetchAll(PDO::FETCH_COLUMN);
            if (!empty($users)) {
                echo "Records exist for other users: " . implode(', ', $users) . "\n";
            }
        }
    }
    
} catch (PDOException $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
}

echo "</pre>";
?>