
<?php
require_once '../../autoload.php';
//require('../GetDataFromDB.php');

$conn = get_dbconnection();
$toProcess = true;

$params = [];
$paramIndex = 1;

function add_param(&$params, &$paramIndex, $value) {
    $params[] = $value;
    return '$' . $paramIndex++;
}

function sanitize_int_list_array($str) {
    return array_map('intval', explode(',', $str));
}

function is_valid_date($date) {
    return preg_match('/^\d{4}-\d{2}-\d{2}$/', $date);
}

/* Pagination DataTables */
$draw   = intval($_GET['draw'] ?? 0);
$start  = intval($_GET['start'] ?? 0);
$length = intval($_GET['length'] ?? 10);
$limit  = ($length != -1) ? " OFFSET $start LIMIT $length" : "";

/* ORDER BY whitelist */
$columns = $_GET['columns'] ?? [];
$order   = $_GET['order'] ?? [];
$allowedCols = [
    'date' => 'date',
    'pname' => 'pname',
    'mname' => 'mname',
    'description' => 'description',
    'pid' => 'pid',
    'accessionnumber' => 'accessionnumber'
];

$orderColIndex = intval($order[0]['column'] ?? 0);
$orderKey = $columns[$orderColIndex]['data'] ?? 'date';
$orderCol = $allowedCols[$orderKey] ?? 's.date';
$orderDir = strtoupper($order[0]['dir'] ?? 'ASC');
$orderDir = ($orderDir === 'DESC') ? 'DESC' : 'ASC';
$orderby = " ORDER BY $orderCol $orderDir";

/* WHERE dynamique */
$where = [];
$modtype = $_GET['modality'] ?? '';
$isri_safe = pg_escape_literal($conn, $modtype);

switch($modtype) {
    case 'RX':
        $where[] = "v.modalitytitle NOT IN ('CT','MG','NM')";
        break;
    case 'CT':
    case 'MG':
    case 'NM':
        $p = add_param($params, $paramIndex, $modtype);
        $where[] = "v.modalitytitle = $p";
        break;
}

if (!empty($_GET["date1"]) && is_valid_date($_GET["date1"])) {
    $p = add_param($params, $paramIndex, $_GET["date1"]);
    $where[] = "s.date::date >= to_date($p,'YYYY-MM-DD')";
}
if (!empty($_GET["date2"]) && is_valid_date($_GET["date2"])) {
    $p = add_param($params, $paramIndex, $_GET["date2"]);
    $where[] = "s.date::date <= to_date($p,'YYYY-MM-DD')";
}
if (!empty($_GET["pname"])) {
    $p = add_param($params, $paramIndex, '%' . $_GET["pname"] . '%');
    $where[] = "p.name ILIKE $p";
}
if (!empty($_GET["acn"])) {
    $p = add_param($params, $paramIndex, $_GET["acn"] . '%');
    $where[] = "s.accessionnumber ILIKE $p";
}

/* modalities IN */
if (!empty($_GET['modalities'])) {
    $list = sanitize_int_list_array($_GET['modalities']);
    if (!empty($list)) {
        $ph = [];
        foreach ($list as $val) $ph[] = add_param($params, $paramIndex, $val);
        $where[] = "v.mod_id IN (" . implode(',', $ph) . ")";
    }
}

/* study description */
if (!empty($_GET['study'])) {
    $p = add_param($params, $paramIndex, '%' . $_GET['study'] . '%');
    $where[] = "s.description_auto ILIKE $p";
}

/* dhx_filter */
if (!empty($_GET["dhx_filter"])) {
    foreach ($_GET["dhx_filter"] as $k => $v) {
        if ($v === '') continue;
        switch($k) {
            case 8:
                $p = add_param($params, $paramIndex, $v . '%');
                $where[] = "p.id ILIKE $p";
                break;
            case 10:
                $list = sanitize_int_list_array($v);
                if (!empty($list)) {
                    $ph = [];
                    foreach ($list as $val) $ph[] = add_param($params, $paramIndex, $val);
                    $where[] = "v.mod_id IN (" . implode(',', $ph) . ")";
                }
                break;
            case 11:
                $p = add_param($params, $paramIndex, '%' . $v . '%');
                $where[] = "s.description_auto ILIKE $p";
                break;
        }
    }
}

/* alert */
$alert = [];
if (($_GET['alertraised'] ?? '') === 'true') {
    $alert[] = "a.stu_id IS NOT NULL";
    if (!empty($_GET['nrdid'])) {
        $list = sanitize_int_list_array($_GET['nrdid']);
        if (!empty($list)) {
            $ph = [];
            foreach ($list as $val) $ph[] = add_param($params, $paramIndex, $val);
            $alert[] = "a.nrd_id IN (" . implode(',', $ph) . ")";
        }
    }
}
if (($_GET['unreadalert'] ?? '') === 'true') {
    $alert[] = "a.ack_id IS NULL";
}

/* session filter */
if (!empty($_SESSION['filter_modalitylist'])) {
    $list = sanitize_int_list_array($_SESSION['filter_modalitylist']);
    if (!empty($list)) {
        $ph = [];
        foreach ($list as $val) $ph[] = add_param($params, $paramIndex, $val);
        $where[] = "v.mod_id IN (" . implode(',', $ph) . ")";
    }
}

/* final WHERE */
$whereSQL = "";
if (!empty($where) || !empty($alert)) {
    $whereSQL = " WHERE " . implode(" AND ", array_merge($where, $alert));
}

/* =========================
			QUERY
========================= */
$sql = "SELECT *
FROM (
    SELECT DISTINCT
        CASE WHEN a.stu_id IS NOT NULL THEN './dbconnector/examlist/db_get_alertforstudy.php?stuid=' || s.stu_id ELSE '' END AS subrow,
        s.stu_id,
        a.ack_id,
        CASE WHEN (m.ri OR v.ri) THEN 'RI' ELSE $isri_safe END AS isri,
        to_char(s.date,'YYYYMMDDHH24MI') AS date,
        p.pat_id,
        p.id AS pid,
        replace(p.name,'^',' ') AS pname,
        v.name AS mname,
        s.description_auto AS description,
        COALESCE(ROUND(s.dap_total::numeric,2)::text,'') ||
        COALESCE(COALESCE(' (' || COALESCE(ROUND(s.fluoro_dap_total::numeric,2)::text || '|' || COALESCE(s.total_fluoro_time/1000 || 's',''),ROUND(s.fluoro_dap_total::numeric,2)::text,s.total_fluoro_time/1000||'s',NULL) || ')','')) AS dap_total,
        ROUND(s.dgm_auto::numeric,2) AS dgm_auto,
        ROUND(s.dlp_auto::numeric,2) AS dlp_auto,
        s.radioelement_auto,
        ROUND(s.dosenm_auto::numeric,2) AS dosenm_auto,
        COALESCE(MAX(a.value/NULLIF(a.nrd_value,0)) OVER(PARTITION BY s.stu_id),0) AS alertraise,
        s.accessionnumber,
        COUNT(*) OVER() AS recordsFiltered
    FROM study s
    INNER JOIN patient p ON p.pat_id = s.pat_id
    INNER JOIN v_modality_config v ON s.pro_id = v.pro_id
    INNER JOIN master_study_description m ON m.mstud_id = s.mstud_id_auto
    LEFT JOIN alertwarning a ON s.stu_id = a.stu_id
    $whereSQL
) sub
$orderby
$limit";

/* =========================
   COUNT pour recordsFiltered
========================= */
$countSql = "SELECT COUNT(DISTINCT s.stu_id) AS recordsFiltered
FROM study s
INNER JOIN patient p ON p.pat_id = s.pat_id
INNER JOIN v_modality_config v ON s.pro_id = v.pro_id
INNER JOIN master_study_description m ON m.mstud_id = s.mstud_id_auto
LEFT JOIN alertwarning a ON s.stu_id = a.stu_id
$whereSQL";

$resCount = pg_query_params($conn, $countSql, $params);
$recordsFiltered = intval(pg_fetch_result($resCount, 0, 'recordsFiltered'));

/* =========================
   EXEC
========================= */
$recordsTotal = 1000;
$data = [];
if ($toProcess) {
    $res = pg_query_params($conn, $sql, $params);
    $data = pg_fetch_all($res) ?: [];
}

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

