<?php
require_once __DIR__ . '/config.php';
$me = must_auth();
ensure_app_schema();
// Get the last message for every unique conversation. Timestamps are stored/displayed as UTC ISO strings.
$st = db()->prepare("
    SELECT m.id, m.from_user, m.to_user,
           CASE WHEN m.from_user=? THEN m.to_user ELSE m.from_user END AS peer,
           m.cipher_text, m.iv, m.msg_type, m.status,
           DATE_FORMAT(m.sent_at,'%Y-%m-%dT%H:%i:%sZ') AS sent_at,
           IF(u.display_name='',u.username,u.display_name) AS display_name,
           u.avatar, u.about,
           COALESCE(NULLIF(p.timezone,''),u.timezone) AS timezone,
           COALESCE(NULLIF(p.country,''),u.country) AS country,
           IF(p.last_seen>=DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 SECOND),1,0) AS online,
           DATE_FORMAT(p.last_seen,'%Y-%m-%dT%H:%i:%sZ') AS peer_last_seen,
           (SELECT COUNT(*) FROM messages m2
            WHERE m2.to_user=? AND m2.from_user=CASE WHEN m.from_user=? THEN m.to_user ELSE m.from_user END
              AND m2.status!='read'
              AND (m2.deleted_for IS NULL OR (m2.deleted_for NOT LIKE ? AND m2.deleted_for NOT LIKE ?))) AS unread
    FROM messages m
    JOIN users u ON u.username=CASE WHEN m.from_user=? THEN m.to_user ELSE m.from_user END
    LEFT JOIN presence p ON p.username=CASE WHEN m.from_user=? THEN m.to_user ELSE m.from_user END
    WHERE (m.from_user=? OR m.to_user=?)
      AND (m.deleted_for IS NULL OR (m.deleted_for NOT LIKE ? AND m.deleted_for NOT LIKE ?))
    ORDER BY m.id DESC
");
$st->execute(array($me,$me,$me,'%"'.$me.'"%','%"__all__"%',$me,$me,$me,$me,'%"'.$me.'"%','%"__all__"%'));
$rows  = $st->fetchAll();
$seen  = array(); $result = array();
foreach ($rows as $r) {
    if (!isset($seen[$r['peer']])) { $seen[$r['peer']] = true; $result[] = $r; }
}
ok(array('conversations' => $result));
