<?php

/*
 * students_json.php
 *
 * Reads the students table from the online SQLite database
 * and returns the records as JSON.
 */


/*
 * Return JSON
 */
header('Content-Type: application/json; charset=utf-8');


/*
 * Path to the SQLite database.
 *
 * IMPORTANT:
 * Change this path if database.db is located somewhere else.
 */
$dbFile = __DIR__ . '/database.db';


/*
 * Check that database exists.
 */
if (!file_exists($dbFile)) {

    http_response_code(500);

    echo json_encode([
        'success' => false,
        'message' => 'Database file not found.'
    ]);

    exit;
}


try {

    /*
     * Connect to SQLite database.
     */
    $db = new PDO('sqlite:' . $dbFile);

    $db->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );


    /*
     * Get students.
     *
     * picture is included because we want the Android/PHPAndro
     * application to eventually be able to display the photo.
     */
    $stmt = $db->query("
        SELECT
            user_id,
            name,
            picture,
            sex,
            created_at
        FROM students
        ORDER BY user_id ASC
    ");


    $students = [];


    /*
     * Convert every student into an array.
     */
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        $student = [

            'user_id' => $row['user_id'],

            'name' => $row['name'],

            'sex' => $row['sex'],

            'created_at' => $row['created_at']

        ];


        /*
         * --------------------------------------------------
         * PICTURE
         * --------------------------------------------------
         *
         * SQLite stores picture as BLOB.
         *
         * JSON cannot directly contain binary BLOB data,
         * therefore we convert it to Base64.
         *
         * The PHPAndro application can later convert this
         * Base64 data back into an image.
         */

        if (
            isset($row['picture']) &&
            $row['picture'] !== null &&
            $row['picture'] !== ''
        ) {

            $student['picture'] = base64_encode(
                $row['picture']
            );
        } else {

            $student['picture'] = null;
        }


        $students[] = $student;
    }


    /*
     * Return the complete list.
     */
    echo json_encode(
        $students,
        JSON_UNESCAPED_UNICODE
    );
} catch (PDOException $e) {

    http_response_code(500);

    echo json_encode([

        'success' => false,

        'message' => 'Database error.'

    ]);
}
