<?php
include "../../src/config.php";

if (!auth::user()) {
    endrequest("You dont have access to this api");
}

if (!isset($_GET["title"]) || !isset($_GET["sender"])) {
    endrequest("Invalid Request");
}


$sql = "SELECT 
    l.*,
    DATE_FORMAT(l.timestamp, '%H:%i') as time,
    DATE_FORMAT(l.received, '%y-%m-%d') as received_text,
    DATE_FORMAT(l.date, '%y %b %d') as date,
    u.name as user_name, 
    u.id as user_id,
    s.name as source_name,
    IF(l.sender IS NULL, l.sender2, sender.name) as sender_name,
    f.shortname as forwarded_shortname,
    f.codename as forwarded_codename,
    a.shortname as assigned_shortname,
    a.code as assigned_code,
    states.name as state_name
FROM 
    mis_letters as l
INNER JOIN users as u on l.user = u.id
INNER JOIN mis_letter_sources as s on l.source = s.id
LEFT JOIN institutions as sender on l.sender = sender.id
LEFT JOIN institutions as f on l.forwarded = f.id
LEFT JOIN users as a on l.assigned = a.id
LEFT JOIN mis_letter_states as states on states.id = l.state
having 
    l.title LIKE :title and
    sender_name LIKE :sender
order by l.id desc ";

$data['title'] = "%" . $_GET['title'] . "%";
$data['sender'] = "%" . $_GET['sender'] . "%";

$response = datatable::query($sql, $data);

foreach ($response["data"] as $key => $value) {
    $response["data"][$key]["title"] = str_replace($_GET['title'], "<span class='text-searched'>" . $_GET['title'] . "</span>", $value["title"]);
    $response["data"][$key]["sender_name"] = str_replace($_GET['sender'], "<span class='text-searched'>" . $_GET['sender'] . "</span>", $value["sender_name"]);
}

echo json_encode($response);
