<?php
    header('Content-Type: application/json');
    require_once '../DB/db_credentials.php';
    require_once '../DB/db_connection.php';
    // global $dbc; // needs to be set globally so $dbc is accessible in this file too

    $query = "SELECT 
                s.pk_scheduleID, 
                f.pk_filmID, 
                f.title, 
                s.startTime, 
                s.endTime, 
                CONCAT(u.firstName, ' ', u.lastName) AS scheduled_by
            FROM 
                citeLeParis_schedule s
            INNER JOIN 
                citeLeParis_film f ON s.fk_filmID = f.pk_filmID
            INNER JOIN 
                citeLeParis_user u ON s.fk_userID = u.pk_userID
             "; 

    if ($stmt = mysqli_prepare($dbc, $query)) {
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $scheduleID, $filmID, $title, $startTime, $endTime, $scheduled_by);

        // fetch results into $events array
        $events = [];

        while (mysqli_stmt_fetch($stmt)) {
            // calculate duration in minutes
            $duration = (strtotime($endTime) - strtotime($startTime)) / 60;
    
            $events[] = [
                "scheduleID" => $scheduleID,
                "filmID" => $filmID,  
                "title" => $title,
                "start" => $startTime,
                "end" => $endTime,
                "scheduled_by" => $scheduled_by,
                "duration" => $duration . ' minutes'
            ];
        }

        mysqli_stmt_close($stmt);

        echo json_encode($events);
    } else {
        echo json_encode(["error" => "Failed to prepare the query"]);
    }
?>