<?php
include('dbconnection.php');
mysqli_set_charset($con, "utf8mb4");
// Twilio number (your number)
$myNumber = '+13322308524';

// Other party (sender) must be passed in URL
  $sender = isset($_GET['sender']) ? trim($_GET['sender']) : '';

if (empty($sender)) {
    die(json_encode(["error" => "Missing required parameter: sender"]));
}

// Escape values
$senderEscaped   = $con->real_escape_string($sender);
$myNumberEscaped = $con->real_escape_string($myNumber);

// SMS messages query (between myNumber and sender)
$smsSql = "
    SELECT 
        id, 
        Sender, 
        Receiver, 
        Body, 
        Status, 
        Type, 
        Message_Id, 
        Seen, 
        created_at AS Date_time, 
        'message' AS chat_type,
        NULL AS Duration,
        Image
    FROM SMS
    WHERE (Sender = '$senderEscaped' AND Receiver = '$myNumberEscaped')
       OR (Sender = '$myNumberEscaped' AND Receiver = '$senderEscaped')
";

// Calls query (between myNumber and sender)
$callsSql = "
    SELECT 
        id,
        caller AS Sender,
        receiver AS Receiver,
        CallStatus AS Body,
        '' AS Status,
        CallStatus AS Type,
        '' AS Message_Id,
        NULL AS Seen,
        Date_time,
        'call' AS chat_type,
        Duration,
        NULL AS Image
    FROM Calls
    WHERE (caller = '$senderEscaped' AND receiver = '$myNumberEscaped')
       OR (caller = '$myNumberEscaped' AND receiver = '$senderEscaped')
";

// Combine SMS + Calls, sort chronologically
$combinedSql = "($smsSql) UNION ALL ($callsSql) ORDER BY
    CASE 
       WHEN LOWER(Date_time) LIKE '%am' OR LOWER(Date_time) LIKE '%pm' THEN 
           STR_TO_DATE(LOWER(Date_time), '%d-%m-%Y %h:%i:%s%p')
       ELSE 
           Date_time
    END ASC";

$result = $con->query($combinedSql);
$rows = [];

if ($result) {
    while($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
}
header('Content-Type: application/json; charset=utf-8');
echo json_encode($rows, JSON_UNESCAPED_UNICODE);
?>
