<?php
include('db.php');

// Subquery to get latest message per sender
// $sql = "SELECT s1.*
// FROM SMS s1
// INNER JOIN (
//     SELECT Sender, MAX(STR_TO_DATE(created_at, '%d-%m-%Y %h:%i:%sam')) AS max_created
//     FROM SMS
//     WHERE Type = 'Received'
//     GROUP BY Sender
// ) s2
// ON s1.Sender = s2.Sender
//   AND STR_TO_DATE(s1.created_at, '%d-%m-%Y %h:%i:%sam') = s2.max_created
// WHERE s1.Type = 'Received'
// ORDER BY STR_TO_DATE(s1.created_at, '%d-%m-%Y %h:%i:%sam') DESC
//                 ";
                
                
 $sql = "
SELECT s1.*, c.name AS contact_name
FROM SMS s1
LEFT JOIN Contact c ON TRIM(s1.Sender) = TRIM(c.from_number)
INNER JOIN (
    SELECT Sender, MAX(STR_TO_DATE(created_at, '%d-%m-%Y %h:%i:%s%p')) AS max_created
    FROM SMS
    WHERE Type = 'Received'
    GROUP BY Sender
) s2 ON s1.Sender = s2.Sender 
     AND STR_TO_DATE(s1.created_at, '%d-%m-%Y %h:%i:%s%p') = s2.max_created
WHERE s1.Type = 'Received'
ORDER BY s2.max_created DESC
LIMIT 25;
";



$result = $conn->query($sql);
$conversations = [];

if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()) {
    $conversations[] = $row;
  }
}

echo json_encode($conversations);
