<?php
include('dbconnection.php');
$sql = "SELECT 
    s.Sender, 
    s.Body, 
    s.created_at,
    s.Seen,
    c.name AS contact_name
FROM SMS s
LEFT JOIN Contact c ON c.from_number = s.Sender
INNER JOIN (
    SELECT Sender, MAX(
        CASE
            WHEN created_at LIKE '__-__-____%' 
                THEN STR_TO_DATE(created_at, '%d-%m-%Y %h:%i:%s%p')
            ELSE STR_TO_DATE(created_at, '%Y-%m-%d %H:%i:%s')
        END
    ) AS max_created
    FROM SMS
    GROUP BY Sender
) latest 
ON s.Sender = latest.Sender 
AND (
    (created_at LIKE '__-__-____%' 
        AND STR_TO_DATE(s.created_at, '%d-%m-%Y %h:%i:%s%p') = latest.max_created)
    OR
    (created_at LIKE '____-__-__%' 
        AND STR_TO_DATE(s.created_at, '%Y-%m-%d %H:%i:%s') = latest.max_created)
)
ORDER BY latest.max_created DESC;
";
    
    $result = $con->query($sql);
    
    $senders = [];
    if ($result && $result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $senders[] = [
                            "Sender" => $row['Sender'],
                            "Body" => $row['Body'],
                            "created_at" => $row['created_at'],
                            "contact_name" => $row['contact_name'] ? $row['contact_name'] : null,
                            "Seen" => $row['Seen'] // Make sure this is from DB
                        ];

        }
    }
    $con->close();
    
    header('Content-Type: application/json');
    echo json_encode($senders);