<?php

header('Content-Type: application/json');

try {

    $db = new PDO(
        'sqlite:' . __DIR__ . '/database.db'
    );

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


    /*
     * --------------------------------------------------
     * FUNCTION TO CHECK TABLE NAME
     * --------------------------------------------------
     */

    function tableExists(
        $db,
        $tableName
    ) {

        $stmt = $db->prepare(
            "
            SELECT name
            FROM sqlite_master
            WHERE type='table'
            AND name=?
            "
        );

        $stmt->execute(
            array($tableName)
        );

        return $stmt->fetchColumn() !== false;
    }


    /*
     * --------------------------------------------------
     * GET procedure_per_examiner
     * --------------------------------------------------
     */

    $procedurePerExaminer = array();

    if (
        tableExists(
            $db,
            'procedure_per_examiner'
        )
    ) {

        $stmt = $db->query(
            "
            SELECT
                id,
                procedure_number,
                is_selected
            FROM procedure_per_examiner
            ORDER BY procedure_number ASC
            "
        );

        $procedurePerExaminer =
            $stmt->fetchAll(
                PDO::FETCH_ASSOC
            );
    }


    /*
     * --------------------------------------------------
     * GET osce_score
     * --------------------------------------------------
     */

    $osceScore = array();

    if (
        tableExists(
            $db,
            'osce_score'
        )
    ) {

        $stmt = $db->query(
            "
            SELECT
                id,
                total_score
            FROM osce_score
            ORDER BY id ASC
            "
        );

        $osceScore =
            $stmt->fetchAll(
                PDO::FETCH_ASSOC
            );
    }


    /*
     * --------------------------------------------------
     * GET examiners_table
     * --------------------------------------------------
     */

    $examinersTable = array();

    if (
        tableExists(
            $db,
            'examiners_table'
        )
    ) {

        $stmt = $db->query(
            "
            SELECT
                id,
                examiner_number
            FROM examiners_table
            ORDER BY examiner_number ASC
            "
        );

        $examinersTable =
            $stmt->fetchAll(
                PDO::FETCH_ASSOC
            );
    }


    /*
     * --------------------------------------------------
     * GET OSCE_procedures
     * --------------------------------------------------
     */

    $osceProcedures = array();

    if (
        tableExists(
            $db,
            'OSCE_procedures'
        )
    ) {

        $stmt = $db->query(
            "
            SELECT
                id,
                table_name
            FROM OSCE_procedures
            ORDER BY id ASC
            "
        );

        $osceProcedures =
            $stmt->fetchAll(
                PDO::FETCH_ASSOC
            );
    }


    /*
     * --------------------------------------------------
     * GET EACH SELECTED PROCEDURE TABLE
     * --------------------------------------------------
     *
     * Example:
     *
     * PROWoundDressing
     * PROAbdominalExamination
     * PROAdministerIntramuscularDrugInjection
     *
     */

    $procedureTables = array();


    foreach (
        $osceProcedures
        as $procedure
    ) {

        if (
            !isset(
                $procedure['table_name']
            )
        ) {
            continue;
        }


        $tableName =
            trim(
                $procedure['table_name']
            );


        /*
         * Security:
         * Only allow table names beginning with PRO
         */

        if (
            strpos(
                $tableName,
                'PRO'
            ) !== 0
        ) {
            continue;
        }


        /*
         * Extra validation
         */

        if (
            !preg_match(
                '/^PRO[A-Za-z0-9_]+$/',
                $tableName
            )
        ) {
            continue;
        }


        /*
         * Check that table exists
         */

        if (
            !tableExists(
                $db,
                $tableName
            )
        ) {
            continue;
        }


        /*
         * Download procedure data
         */

        $stmt = $db->query(
            "
            SELECT
                id,
                activity,
                score_0,
                score_0_25,
                score_0_5,
                score_1,
                alot_mark
            FROM " . $tableName . "
            ORDER BY id ASC
            "
        );


        $rows =
            $stmt->fetchAll(
                PDO::FETCH_ASSOC
            );


        /*
         * Store procedure table
         */

        $procedureTables[$tableName] = $rows;
    }


    /*
     * --------------------------------------------------
     * SUCCESS RESPONSE
     * --------------------------------------------------
     */

    echo json_encode(
        array(

            'success' => true,

            'procedure_per_examiner' =>
            $procedurePerExaminer,

            'osce_score' =>
            $osceScore,

            'examiners_table' =>
            $examinersTable,

            'OSCE_procedures' =>
            $osceProcedures,

            'procedure_tables' =>
            $procedureTables

        ),
        JSON_UNESCAPED_UNICODE
    );
} catch (Exception $e) {

    echo json_encode(
        array(

            'success' => false,

            'message' =>
            $e->getMessage()

        )
    );
}
