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

    // Whitelists

    $allowed_columns = [
        'mail_id', 'acc_id', 'fullname', 'mail', 'role', 'status', 'removed'
    ];

    $allowed_alert_types = ['NRDPATIENTALERT', 'NRDALERT', 'NEWMODIDENT'];

    $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);

    // Whitelist ORDER BY

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

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

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

    // OFFSET/LIMIT sécurisés par intval()
    $limit = ($length != -1) ? " OFFSET {$start} LIMIT {$length}" : '';

    // Validation alert_type par whitelist 

    $alert_type = $_GET['alert_type'] ?? '';
    if(!in_array($alert_type, $allowed_alert_types, true))
    {
        echo $empty_response;
        exit();
    }

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

    $base_sql = "
        WITH t1 AS (
            SELECT mail_id FROM alertmail WHERE type = \$1
        )
        SELECT
            t1.mail_id                              AS mail_id,
            acc_id,
            lastname,
            firstname,
            upper(lastname) || ' ' || firstname     AS fullname,
            mail,
            role,
            status,
            removed
        FROM account
        LEFT JOIN alertmail_account_link aal ON account.acc_id = aal.account_id
        LEFT JOIN t1                         ON t1.mail_id = aal.alert_mail_id
        WHERE removed = false";

    $params = [$alert_type];

    // --- Comptage ---
    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]);

    
    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
    ]);
?>