<?php
require_once '../includes/db.php';

echo "<h2>Comprehensive Debug: Photos and Data</h2>";

$event_id = 3;

echo "<h3>1. Participant Photos Check</h3>";

// Check participants with photos
$stmt = $pdo->prepare("
    SELECT p.id, p.photo, p.first_name, p.last_name, p.name, ep.bib_number
    FROM participants p
    INNER JOIN event_participants ep ON p.id = ep.participant_id
    WHERE ep.event_id = ?
    ORDER BY ep.bib_number
");
$stmt->execute([$event_id]);
$participants = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<p>Total participants in event $event_id: " . count($participants) . "</p>";

$photos_found = 0;
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Bib</th><th>Photo Field</th><th>Photo Exists</th><th>Photo Path</th></tr>";

foreach ($participants as $participant) {
    echo "<tr>";
    echo "<td>" . $participant['id'] . "</td>";
    echo "<td>" . htmlspecialchars($participant['name']) . "</td>";
    echo "<td>" . $participant['bib_number'] . "</td>";
    echo "<td>" . htmlspecialchars($participant['photo']) . "</td>";
    
    $photo_found = false;
    $photo_path = '';
    
    if ($participant['photo']) {
        $possible_paths = [
            __DIR__ . '/../uploads/' . $participant['photo'],
            __DIR__ . '/../uploads/participants/' . $participant['photo'],
            __DIR__ . '/../uploads/profile_stills/' . $participant['photo']
        ];
        
        foreach ($possible_paths as $path) {
            if (file_exists($path)) {
                $photo_found = true;
                $photo_path = $path;
                $photos_found++;
                break;
            }
        }
    }
    
    echo "<td>" . ($photo_found ? "✓" : "✗") . "</td>";
    echo "<td>" . htmlspecialchars($photo_path) . "</td>";
    echo "</tr>";
}
echo "</table>";
echo "<p><strong>Photos found: $photos_found</strong></p>";

echo "<h3>2. Database Structure Check</h3>";

// First, let's see what tables exist
$stmt = $pdo->query("SHOW TABLES");
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

echo "<p>Available tables in database:</p>";
echo "<ul>";
foreach ($tables as $table) {
    echo "<li>$table</li>";
}
echo "</ul>";

echo "<h3>3. Scores Data Check</h3>";

// Let's try a simpler query first to see what score-related tables exist
try {
    // Check if scores table exists
    if (in_array('scores', $tables)) {
        $stmt = $pdo->prepare("SELECT * FROM scores WHERE 1=1 LIMIT 5");
        $stmt->execute();
        $scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<p>Direct scores table - found " . count($scores) . " records</p>";
        if (!empty($scores)) {
            echo "<pre>" . print_r($scores[0], true) . "</pre>";
        }
    } else {
        echo "<p style='color: red;'>No 'scores' table found</p>";
    }
    
    // Check runs table
    if (in_array('runs', $tables)) {
        $stmt = $pdo->prepare("SELECT * FROM runs LIMIT 5");
        $stmt->execute();
        $runs = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<p>Runs table - found " . count($runs) . " records</p>";
        if (!empty($runs)) {
            echo "<pre>" . print_r($runs[0], true) . "</pre>";
        }
    } else {
        echo "<p style='color: red;'>No 'runs' table found</p>";
    }
    
    // Try to find event participants for our event
    if (in_array('event_participants', $tables)) {
        $stmt = $pdo->prepare("SELECT * FROM event_participants WHERE event_id = ? LIMIT 5");
        $stmt->execute([$event_id]);
        $event_participants = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<p>Event participants for event $event_id: " . count($event_participants) . "</p>";
        if (!empty($event_participants)) {
            echo "<pre>" . print_r($event_participants[0], true) . "</pre>";
        }
    }
    
    // Check event_heat_settings table
    if (in_array('event_heat_settings', $tables)) {
        $stmt = $pdo->prepare("SELECT * FROM event_heat_settings WHERE event_id = ? LIMIT 5");
        $stmt->execute([$event_id]);
        $heat_settings = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<p>Event heat settings for event $event_id: " . count($heat_settings) . "</p>";
        if (!empty($heat_settings)) {
            echo "<pre>" . print_r($heat_settings[0], true) . "</pre>";
        }
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error checking scores: " . $e->getMessage() . "</p>";
}

echo "<h3>3. Table Structure Analysis</h3>";

// Let's check the structure of key tables
$key_tables = ['scores', 'runs', 'event_participants', 'participants', 'event_heat_settings'];

foreach ($key_tables as $table) {
    if (in_array($table, $tables)) {
        echo "<h4>$table table structure:</h4>";
        try {
            $stmt = $pdo->query("DESCRIBE $table");
            $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            echo "<table border='1'>";
            echo "<tr><th>Column</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
            foreach ($columns as $column) {
                echo "<tr>";
                echo "<td>" . $column['Field'] . "</td>";
                echo "<td>" . $column['Type'] . "</td>";
                echo "<td>" . $column['Null'] . "</td>";
                echo "<td>" . $column['Key'] . "</td>";
                echo "<td>" . $column['Default'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            
            // Show sample data
            $stmt = $pdo->query("SELECT * FROM $table LIMIT 3");
            $sample_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if (!empty($sample_data)) {
                echo "<p><strong>Sample data:</strong></p>";
                echo "<pre>" . print_r($sample_data[0], true) . "</pre>";
            }
            
        } catch (Exception $e) {
            echo "<p style='color: red;'>Error describing $table: " . $e->getMessage() . "</p>";
        }
        echo "<br>";
    } else {
        echo "<p style='color: red;'>Table $table does not exist</p>";
    }
}

echo "<h3>4. Alternative Score Query</h3>";

// Try alternative approaches to get scores data
try {
    // Let's try to build a working query step by step
    if (in_array('scores', $tables)) {
        // First, just get raw scores data
        $stmt = $pdo->prepare("SELECT * FROM scores LIMIT 5");
        $stmt->execute();
        $raw_scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<p>Raw scores table data: " . count($raw_scores) . " records</p>";
        if (!empty($raw_scores)) {
            echo "<pre>" . print_r($raw_scores[0], true) . "</pre>";
        }
        
        // Now try to join with other tables if we can figure out the relationships
        if (in_array('event_participants', $tables)) {
            // Let's see what columns are available for joining
            echo "<p>Attempting to join scores with event_participants...</p>";
            
            // Try different possible join columns
            $possible_joins = [
                'participant_id',
                'run_id', 
                'event_participant_id'
            ];
            
            foreach ($possible_joins as $join_col) {
                try {
                    if ($join_col == 'participant_id') {
                        $stmt = $pdo->prepare("
                            SELECT s.*, p.name as participant_name, ep.bib_number, ep.event_id
                            FROM scores s
                            JOIN participants p ON s.participant_id = p.id
                            JOIN event_participants ep ON p.id = ep.participant_id
                            WHERE ep.event_id = ?
                            LIMIT 5
                        ");
                    } elseif ($join_col == 'run_id') {
                        $stmt = $pdo->prepare("
                            SELECT s.*, r.*, ep.bib_number, ep.event_id, p.name as participant_name
                            FROM scores s
                            JOIN runs r ON s.run_id = r.id
                            JOIN event_participants ep ON r.event_participant_id = ep.id
                            JOIN participants p ON ep.participant_id = p.id
                            WHERE ep.event_id = ?
                            LIMIT 5
                        ");
                    } else {
                        $stmt = $pdo->prepare("
                            SELECT s.*, ep.bib_number, ep.event_id, p.name as participant_name
                            FROM scores s
                            JOIN event_participants ep ON s.event_participant_id = ep.id
                            JOIN participants p ON ep.participant_id = p.id
                            WHERE ep.event_id = ?
                            LIMIT 5
                        ");
                    }
                    
                    $stmt->execute([$event_id]);
                    $joined_scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    echo "<p>✓ Join using $join_col worked! Found " . count($joined_scores) . " records</p>";
                    if (!empty($joined_scores)) {
                        echo "<pre>" . print_r($joined_scores[0], true) . "</pre>";
                    }
                    break; // Stop on first successful join
                    
                } catch (Exception $join_error) {
                    echo "<p>✗ Join using $join_col failed: " . $join_error->getMessage() . "</p>";
                }
            }
        }
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error with alternative query: " . $e->getMessage() . "</p>";
    echo "<p>Error details: " . $e->getTraceAsString() . "</p>";
}

echo "<h3>5. Summary Table Configurations Check</h3>";

try {
    $stmt = $pdo->prepare("
        SELECT * FROM result_configurations 
        WHERE event_id = ? AND view_type = 'summary_table'
        ORDER BY name
    ");
    $stmt->execute([$event_id]);
    $configs = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<p>Summary table configurations found: " . count($configs) . "</p>";
    
    if (!empty($configs)) {
        echo "<table border='1'>";
        echo "<tr><th>Config ID</th><th>Name</th><th>View Type</th></tr>";
        foreach ($configs as $config) {
            echo "<tr>";
            echo "<td>" . $config['id'] . "</td>";
            echo "<td>" . htmlspecialchars($config['name']) . "</td>";
            echo "<td>" . $config['view_type'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
} catch (PDOException $e) {
    echo "<p style='color: red;'>result_configurations table not found: " . $e->getMessage() . "</p>";
}

echo "<h3>6. Test File Generation</h3>";

require_once 'publish_generators.php';

$temp_dir = sys_get_temp_dir() . '/stylescore_debug_' . time();
if (!is_dir($temp_dir)) {
    mkdir($temp_dir, 0777, true);
}

echo "<p>Testing file generation in: $temp_dir</p>";

try {
    $files = generateHTMLDashboard($event_id, $temp_dir, $pdo);
    echo "<p>Generated " . count($files) . " files:</p>";
    
    echo "<table border='1'>";
    echo "<tr><th>Remote Name</th><th>Local Path</th><th>Exists</th><th>Size</th><th>Type</th></tr>";
    
    foreach ($files as $file) {
        $exists = file_exists($file['local']);
        $size = $exists ? filesize($file['local']) : 0;
        $type = pathinfo($file['remote'], PATHINFO_EXTENSION);
        
        echo "<tr>";
        echo "<td>" . htmlspecialchars($file['remote']) . "</td>";
        echo "<td>" . htmlspecialchars(basename($file['local'])) . "</td>";
        echo "<td>" . ($exists ? "✓" : "✗") . "</td>";
        echo "<td>$size bytes</td>";
        echo "<td>$type</td>";
        echo "</tr>";
    }
    echo "</table>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error generating files: " . $e->getMessage() . "</p>";
}

// Cleanup
if (is_dir($temp_dir)) {
    $files = glob($temp_dir . '/*');
    foreach ($files as $file) {
        if (is_file($file)) {
            unlink($file);
        }
    }
    
    // Also clean photos subdirectory
    $photos_dir = $temp_dir . '/photos';
    if (is_dir($photos_dir)) {
        $photo_files = glob($photos_dir . '/*');
        foreach ($photo_files as $file) {
            if (is_file($file)) {
                unlink($file);
            }
        }
        rmdir($photos_dir);
    }
    
    rmdir($temp_dir);
    echo "<p>Cleaned up temp directory</p>";
}
?>
