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

    // --------------------------------
    // Whitelist colonnes ORDER BY
    // ------------------------------
    $allowed_columns = [
        'stu_id', 'study', 'patientname', 'nbphase', 'classification',
        'age', 'imc', 'height', 'pat_weight', 'modality', 'radiologist',
        'technologist', 'dlp', 'study_description', 'pds', 'pds_3d',
        'dgm', 'ccam', 'patient_id', 'accessionnumber', 'fluoro_time',
        'fluoro_dap', 'comment_1', 'comment_2', 'comment_3',
        'dose_rp_total', 'fluoro_dose_rp_total', 'birthdate', 'studydate'
    ];

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

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

    $orderby = '';
    if(isset($_GET['columns']))
    {
        $columns  = $_GET['columns'] ?? [];
        $order    = $_GET['order']   ?? [];
        $orderCol = intval($order[0]['column'] ?? 0);

        $orderColName = $columns[$orderCol]['data'] ?? 'studydate';
        if(!in_array($orderColName, $allowed_columns, true))
            $orderColName = 'studydate';

        $orderDir = (strtoupper($order[0]['dir'] ?? '') === 'DESC') ? 'DESC' : 'ASC';
        $orderby  = " ORDER BY {$orderColName} {$orderDir}";
    }

    
    $limit = ($length != -1) ? " OFFSET {$start} LIMIT {$length}" : '';

   
    $from = $_GET['from'] ?? '';
    $to   = $_GET['to']   ?? '';

    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from) || !strtotime($from) ||
       !preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)   || !strtotime($to))
    {
        echo $empty_response;
        exit();
    }

    function validate_int_list(string $raw): array
    {
        if(trim($raw) === '') return [];
        $items = explode(',', urldecode($raw));
        $safe  = [];
        foreach($items as $item)
        {
            $item = trim($item);
            if(preg_match('/^\d+$/', $item)) // entier >= 0
                $safe[] = intval($item);
        }
        return $safe;
    }

    // mstuid
    $mstuid_list = validate_int_list($_GET['mstuid'] ?? '');
    $studymaster_filter = '';
    if(!empty($mstuid_list))
        $studymaster_filter = ' AND mstud_id_auto IN (' . implode(',', $mstuid_list) . ')';

    // imc
    $imc_list = validate_int_list($_GET['imc'] ?? '');
    $imc_filter = '';
    if(!empty($imc_list))
        $imc_filter = ' AND f_get_imc_id(pat_size, pat_weight) IN (' . implode(',', $imc_list) . ')';

    // modid
    $modid_list = validate_int_list($_GET['modid'] ?? '');
    $modality_filter = '';
    if(!empty($modid_list))
        $modality_filter = ' AND mod_id IN (' . implode(',', $modid_list) . ')';

    // ---------------------------------
    // Filtre âge : indices 0-4 mappés
    // --------------------------------
    $age_filter = '';
    if(isset($_GET['age']) && $_GET['age'] !== '')
    {
        $ageRanges = [
            0 => ['min' => 16, 'max' => -1],
            1 => ['min' => 11, 'max' => 15],
            2 => ['min' => 6,  'max' => 11],
            3 => ['min' => 1,  'max' => 5],
            4 => ['min' => 0,  'max' => 1],
        ];
        $selectedAges  = explode(',', urldecode($_GET['age']));
        $ageConditions = [];
        foreach($selectedAges as $age)
        {
            $age = intval(trim($age)); // cast strict : seuls les indices 0-4 passeront array_key_exists
            if(array_key_exists($age, $ageRanges))
            {
                $range     = $ageRanges[$age];
                $condition = '';
                if($range['min'] != -1)
                    $condition .= "EXTRACT(year FROM age(studydate, birthdate))::integer >= {$range['min']}";
                if($range['max'] != -1)
                {
                    if($condition !== '') $condition .= ' AND ';
                    $condition .= "EXTRACT(year FROM age(studydate, birthdate))::integer <= {$range['max']}";
                }
                if($condition !== '')
                    $ageConditions[] = "({$condition})";
            }
        }
        if(!empty($ageConditions))
            $age_filter = ' AND (' . implode(' OR ', $ageConditions) . ')';
    }

    $base_sql = "
        SELECT *
        FROM v_export_csv
        WHERE studydate::date >= to_date(\$1, 'YYYY-MM-DD')
          AND studydate::date <= to_date(\$2, 'YYYY-MM-DD')
        {$studymaster_filter}
        {$imc_filter}
        {$age_filter}
        {$modality_filter}";

    $params = [$from, $to];

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

    if(!pg_prepare($conn, 'stmt_count', "SELECT COUNT(*) FROM ({$base_sql}) 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', $params);
    $recordsFiltered = 0;
    if($count_result)
        $recordsFiltered = intval(pg_fetch_row($count_result)[0]);

    // --- Requête préparée : données paginées ---
    if(!pg_prepare($conn, 'stmt_data', $base_sql . $orderby . $limit))
    {
        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
    ]);
?>