<?php
include "/var/www/nclive/backend/config.php";
$outputFile = __DIR__ . "/exam_data.json";  // File to store JSON

// Run the query
$sql = "SELECT 
    el.exam_id, 
    el.entity_id, 
    el.display_text, 
    el.display_text_dates, 
    el.links, 
    el.date_type, 
    el.date, 
    ee.name, 
    e.off_web_link, 
    jt.sp_exam_id_single AS split_sp_exam_id
FROM sp_exam_links AS el
LEFT JOIN sp_exam_entity AS ee ON ee.id = el.entity_id
LEFT JOIN sp_exam AS e ON e.exam_id = el.exam_id
JOIN JSON_TABLE(
    CONCAT('[', e.sp_exam_id, ']'),
    \"\$[*]\" COLUMNS (sp_exam_id_single INT PATH \"\$\")
) AS jt
WHERE e.sp_exam_id != 0
ORDER BY ee.display_order ASC";

$stmt = $db->query($sql);
$rows = $stmt->fetchAll();

// Organize by sp_exam_id for fast API access
$organized = [];
foreach ($rows as $row) {
    $spId = $row['split_sp_exam_id'];
    unset($row['split_sp_exam_id']); // optional, keep clean
    $organized[$spId][] = $row;
}

// Save as JSON
file_put_contents($outputFile, json_encode($organized, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));

echo "✅ JSON file generated successfully at: $outputFile\n";