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

    // --- Validation de draw, start, length (entiers) ---
    $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 = ['pat_id', 'id', 'name', 'birthdate', 'age', 'accessionnumber'];
    $orderColName = $columns[$orderCol]['data'] ?? 'pat_id';
    if(!in_array($orderColName, $allowed_columns, true))
        $orderColName = 'pat_id';

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

    // ORDER BY et LIMIT/OFFSET ne supportent pas les paramètres liés en PostgreSQL —
    // sécurisés par whitelist et intval() ci-dessus
    $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();
    }

    // -------------------------------------------------------
    // Définition des requêtes préparées selon le contexte
    // -------------------------------------------------------

    $base_sql = '';
    $params   = [];

    if(isset($_GET['value']) && $_GET['value'] !== '')
    {
        // $1 = valeur exacte (accessionnumber, patient.id)
        // $2 = valeur ILIKE  (patient.name%)
        $base_sql = "
            WITH filtered_study AS (
                SELECT pat_id, accessionnumber
                FROM study
                WHERE study.accessionnumber = \$1
            ),
            filtered_patient AS (
                SELECT pat_id, id, name,
                       to_char(birthdate, 'DD/MM/YYYY') AS birthdate_formatted,
                       extract(year FROM age(now(), birthdate)) AS age
                FROM patient
                WHERE patient.id = \$1
                   OR patient.name ILIKE \$2
            )
            SELECT DISTINCT
                coalesce(filtered_patient.pat_id, patient.pat_id)                                       AS pat_id,
                coalesce(filtered_patient.id,     patient.id)                                           AS id,
                coalesce(filtered_patient.name,   patient.name)                                         AS name,
                coalesce(filtered_patient.birthdate_formatted, to_char(patient.birthdate,'DD/MM/YYYY')) AS birthdate,
                coalesce(filtered_patient.age, extract(year FROM age(now(), patient.birthdate)))        AS age,
                filtered_study.accessionnumber                                                          AS accessionnumber
            FROM filtered_patient
            FULL OUTER JOIN filtered_study ON filtered_study.pat_id = filtered_patient.pat_id
            LEFT JOIN       patient         ON patient.pat_id        = filtered_study.pat_id";

        $params = [$_GET['value'], $_GET['value'] . '%'];
    }
    elseif(isset($_GET['stuid']) && $_GET['stuid'] !== '')
    {
        $raw_stuid = $_GET['stuid'];
        if(!preg_match('/^\d+$/', $raw_stuid) || intval($raw_stuid) <= 0)
        {
            echo $empty_response;
            exit();
        }

        $base_sql = "
            WITH filtered_study AS (
                SELECT pat_id, accessionnumber
                FROM study
                WHERE study.stu_id = \$1
            )
            SELECT DISTINCT
                patient.pat_id                                   AS pat_id,
                patient.id                                       AS id,
                patient.name                                     AS name,
                to_char(patient.birthdate, 'DD/MM/YYYY')        AS birthdate,
                extract(year FROM age(now(), patient.birthdate)) AS age,
                filtered_study.accessionnumber                   AS accessionnumber
            FROM filtered_study
            JOIN patient ON patient.pat_id = filtered_study.pat_id";

        $params = [intval($raw_stuid)];
    }
    else
    {
        echo $empty_response;
        exit();
    }

    // --- Requête préparée : comptage ---
    $count_sql = "SELECT COUNT(*) FROM ({$base_sql}) AS count_query";
    if(!pg_prepare($conn, 'stmt_count', $count_sql))
    {
        error_log('pg_prepare stmt_count failed: ' . pg_last_error($conn));
        echo $empty_response;
        exit();
    }
    $count_result = pg_execute($conn, 'stmt_count', $params);
    $recordsFiltered = 0;
    if($count_result)
        $recordsFiltered = intval(pg_fetch_row($count_result)[0]);

    // --- Requête préparée : données paginées et triées ---
    // ORDER BY et LIMIT/OFFSET sont concaténés ici car déjà sécurisés (whitelist + intval)
    $data_sql = $base_sql . $orderby . $limit;
    if(!pg_prepare($conn, 'stmt_data', $data_sql))
    {
        error_log('pg_prepare stmt_data failed: ' . pg_last_error($conn));
        echo $empty_response;
        exit();
    }
    $data_result = pg_execute($conn, 'stmt_data', $params);
    $data = [];
    if($data_result)
        while($row = pg_fetch_assoc($data_result))
            $data[] = $row;

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