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

// Load scoring formats
$formats = $pdo->query("
    SELECT sf.*, 
           sfj.min_judges, sfj.max_judges, sfj.scale_min, sfj.scale_max, 
           sfj.scale_type, sfj.scale_custom, sfj.drop_rule,
           COUNT(DISTINCT sfc.id) as criteria_count,
           COUNT(DISTINCT sfr.id) as results_count
    FROM scoring_formats sf
    LEFT JOIN scoring_format_judges sfj ON sf.format_id = sfj.format_id
    LEFT JOIN scoring_format_criteria sfc ON sf.format_id = sfc.format_id
    LEFT JOIN scoring_format_results sfr ON sf.format_id = sfr.format_id
    GROUP BY sf.format_id
    ORDER BY sf.sport, sf.name
    LIMIT 3
")->fetchAll(PDO::FETCH_ASSOC);

echo "Loaded formats:\n";
foreach ($formats as $format) {
    echo "- {$format['name']} (ID: {$format['format_id']}, criteria_count: {$format['criteria_count']})\n";
}

// Load criteria details for each format
$formatCriteria = [];
if (!empty($formats)) {
    $formatIds = array_column($formats, 'format_id');
    $placeholders = str_repeat('?,', count($formatIds) - 1) . '?';
    echo "\nFormat IDs: " . implode(', ', $formatIds) . "\n";
    echo "Placeholders: $placeholders\n";
    
    $criteriaStmt = $pdo->prepare("
        SELECT format_id, criteria_type, criteria_name, sort_order
        FROM scoring_format_criteria
        WHERE format_id IN ($placeholders)
        ORDER BY format_id, criteria_type, sort_order, criteria_name
    ");
    $criteriaStmt->execute($formatIds);
    $allCriteria = $criteriaStmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "\nFound " . count($allCriteria) . " criteria records\n";
    
    foreach ($allCriteria as $criterion) {
        $formatCriteria[$criterion['format_id']][$criterion['criteria_type']][] = [
            'criteria_name' => $criterion['criteria_name'],
            'sort_order' => $criterion['sort_order']
        ];
    }
}

echo "\nFormatCriteria structure:\n";
print_r($formatCriteria);

// Test the condition
foreach ($formats as $format) {
    $hasContent = !empty($formatCriteria[$format['format_id']]);
    echo "\nFormat {$format['format_id']}: has criteria = " . ($hasContent ? 'YES' : 'NO') . "\n";
}
?>
