<?php
    require_once '../../autoload.php';
    header('Content-Type: application/json; charset=utf-8');

    // --- Validation draw, start, length ---
    $draw   = intval($_GET['draw']   ?? 0);
    $start  = intval($_GET['start']  ?? 0);
    $length = intval($_GET['length'] ?? 10);

    // --- Whitelist colonne de tri ---
    $columns  = $_GET['columns'] ?? [];
    $order    = $_GET['order']   ?? [];
    $orderCol = intval($order[0]['column'] ?? 0);

    $allowed_columns = ['stu_id', 'modalitytitle', 'date', 'groupname', 'accessionnumber', 'description'];
    $orderColName = $columns[$orderCol]['data'] ?? 'date';
    if(!in_array($orderColName, $allowed_columns, true))
        $orderColName = 'date';

    $orderDir = (strtoupper($order[0]['dir'] ?? '') === 'DESC') ? 'DESC' : 'ASC';

    // ORDER BY et LIMIT/OFFSET sécurisés par whitelist + intval
    $orderby = " ORDER BY {$orderColName} {$orderDir}";
    $limit   = ($length != -1) ? " OFFSET {$start} LIMIT {$length}" : '';

    $empty_response = json_encode([
        "draw"            => $draw,
        "recordsTotal"    => 0,
        "recordsFiltered" => 0,
        "data"            => []
    ]);

    $conn = get_dbconnection();
    if(!$conn)
    {
        echo $empty_response;
        exit();
    }

    // --- Validation de patid ---
    if(!isset($_GET['patid']) || $_GET['patid'] === '')
    {
        echo $empty_response;
        exit();
    }

    $raw_patid = $_GET['patid'];
    // patid doit être un entier (positif ou -1 qui est un cas spécial métier)
    if(!preg_match('/^-?\d+$/', $raw_patid) || (intval($raw_patid) <= 0 && intval($raw_patid) != -1))
    {
        echo $empty_response;
        exit();
    }
    $safe_patid = intval($raw_patid);

    // -------------------------------------------------------
    // Cas spécial patid=-1 : aucune donnée retournée (métier)
    // -------------------------------------------------------
    if($safe_patid == -1)
    {
        echo json_encode([
            "draw"            => $draw,
            "recordsTotal"    => 0,
            "recordsFiltered" => 0,
            "data"            => []
        ]);
        exit();
    }

    // -------------------------------------------------------
    // Récupération de id et his_id depuis la base (requête préparée)
    // $safe_patid est un entier validé — pg_prepare pour la cohérence
    // -------------------------------------------------------
    if(!pg_prepare($conn, 'stmt_patient', "SELECT id, his_id FROM patient, institution WHERE pat_id = \$1 AND institution.inst_id = patient.inst_id"))
    {
        error_log('pg_prepare stmt_patient failed: ' . pg_last_error($conn));
        echo $empty_response;
        exit();
    }

    $patient_result = pg_execute($conn, 'stmt_patient', [$safe_patid]);
    if(!$patient_result || !($patient_row = pg_fetch_row($patient_result)))
    {
        echo $empty_response;
        exit();
    }

    $idpatient = $patient_row[0]; // string : id patient (ex: "DUPONT123")
    $his_id    = $patient_row[1]; // string : identifiant HIS

    // Validation de his_id : doit être un entier (clé étrangère numérique)
    if(!preg_match('/^\d+$/', (string)$his_id) || intval($his_id) <= 0)
    {
        error_log("his_id invalide pour pat_id={$safe_patid}: {$his_id}");
        echo $empty_response;
        exit();
    }
    $safe_his_id = intval($his_id);

    // idpatient vient de la base mais est réinjecté dans des requêtes FDW distantes
    // pg_escape_literal protège contre toute valeur inattendue
    $escaped_idpatient = pg_escape_literal($conn, $idpatient);

    // -------------------------------------------------------
    // Construction des requêtes FDW multi-sites
    // get_full_fdw_request génère un SQL composite — on ne peut pas
    // utiliser pg_prepare ici car le SQL est dynamique multi-schémas.
    // La sécurité repose sur :
    //   - $safe_patid     : intval() validé
    //   - $safe_his_id    : intval() validé
    //   - $escaped_idpatient : pg_escape_literal()
    //   - $groupname      : pg_escape_literal() sur chaque site
    // -------------------------------------------------------
    $sessionGLOBALS = [];
    $sessionGLOBALS['eoDACS_FDW'] = $GLOBALS['eoDACS_FDW'];

    $requestlist = [];
    foreach($sessionGLOBALS['eoDACS_FDW'] as $key => &$site)
    {
        if($site['online'] == false)
            continue;

        $sessionGLOBALS['eoDACS_FDW'][$key]['dblinkconnectionname'] = $sessionGLOBALS['eoDACS_FDW'][$key]['servername'];

        if(!array_key_exists($key, $requestlist))
            $requestlist[$key] = [];

        // groupname vient de la config serveur mais on l'échappe par défense en profondeur
        $escaped_groupname = pg_escape_literal($conn, $site['groupname']);

        $requestlist[$key][] = "
            SELECT '{$key}_' || stu_id          AS stu_id,
                   v_modality_config.modalitytitle,
                   to_char(study.date, 'DD/MM/YYYY') AS date,
                   {$escaped_groupname}             AS groupname,
                   accessionnumber,
                   description_auto                  AS description
            FROM {$site['schemaremote']}.study
            JOIN {$site['schemaremote']}.v_modality_config ON study.pro_id = v_modality_config.pro_id
            JOIN {$site['schemaremote']}.patient           ON study.pat_id = patient.pat_id
            JOIN {$site['schemaremote']}.institution       ON institution.inst_id = patient.inst_id
            WHERE patient.id = {$escaped_idpatient}
              AND institution.his_id = {$safe_his_id}";
    }

    if(empty($requestlist))
    {
        echo $empty_response;
        exit();
    }

    $psql = get_full_fdw_request($conn, $requestlist, $sessionGLOBALS);

    if(empty($psql))
    {
        echo $empty_response;
        exit();
    }

    // -------------------------------------------------------
    // Comptage et pagination — requêtes préparées sur le résultat FDW
    // -------------------------------------------------------
    $recordsFiltered = 0;

    if(!pg_prepare($conn, 'stmt_count', "SELECT COUNT(*) FROM ({$psql}) AS count_query"))
    {
        error_log('pg_prepare stmt_count failed: ' . pg_last_error($conn));
        echo $empty_response;
        exit();
    }
    $count_result = pg_execute($conn, 'stmt_count', []);
    if($count_result)
        $recordsFiltered = intval(pg_fetch_row($count_result)[0]);

    if(!pg_prepare($conn, 'stmt_data', $psql . $orderby . $limit))
    {
        error_log('pg_prepare stmt_data failed: ' . pg_last_error($conn));
        echo $empty_response;
        exit();
    }
    $data_result = pg_execute($conn, 'stmt_data', []);
    $data = [];
    if($data_result)
        while($row = pg_fetch_assoc($data_result))
            $data[] = $row;

    echo json_encode([
        "draw"            => $draw,
        "recordsTotal"    => $recordsFiltered,
        "recordsFiltered" => $recordsFiltered,
        "data"            => $data
    ]);
?>
