feat: search by user in room

1 files changed, 53 insertions, 64 deletions

diff --git a/web/index.php b/web/index.php
index b6657c5..392913e 100644
--- a/web/index.php
+++ b/web/index.php
@@ -4,52 +4,25 @@ include_once $_SERVER['DOCUMENT_ROOT'] . '/config.php';
 $db = new PDO(DB_URL, DB_USER, DB_PASS);
 
 $room = $_GET['r'] ?: null;
+$user = $_GET['u'] ?: null;
 $date = $_GET['d'] ?: null;
 
 $limit = min(abs(intval($_GET['l'] ?? '500')), 500);
 $page = abs(intval($_GET['p'] ?? '1') - 1);
 $offset = $limit * $page;
 
-if (isset($room, $date)) {
-    $room = urldecode($room);
-    $date = urldecode($date);
-    $stmt = $db->prepare('SELECT id, `name`, joined_at, departed_at FROM rooms WHERE `name` = ?');
-    $stmt->execute([$room]);
+if (isset($user)) {
+    $stmt = $db->prepare('SELECT id, `nick`, joined_at, departed_at FROM users WHERE `nick` = ?');
+    $stmt->execute([$user]);
 
-    $room = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
-    if (!$room) {
+    $user = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
+    if (!$user) {
         http_response_code(404);
-        exit("No room found.");
+        exit("User not found.");
     }
-    $room['encoded'] = urlencode($room['name']);
+}
 
-    $stmt = $db->prepare("SELECT m.id, u.nick, m.command, m.params, m.tags, m.sent_at
-        FROM messages m
-        JOIN rooms r ON r.id = m.room_id
-        JOIN users u ON u.id = m.user_id
-        WHERE m.room_id = ?
-            AND m.sent_at BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY)
-        ORDER BY sent_at DESC
-        LIMIT $limit OFFSET $offset
-    ");
-    $stmt->execute([$room['id'], $date, $date]);
-    $messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
-
-    // searching for previous message day
-    $stmt = $db->prepare('SELECT m.sent_at AS d
-        FROM messages m
-        JOIN rooms r ON r.id = m.room_id
-        JOIN users u ON u.id = m.user_id
-        WHERE m.room_id = ?
-            AND DATE(m.sent_at) = (
-                SELECT MAX(DATE(sent_at))
-                FROM messages
-                WHERE DATE(sent_at) < ?
-            )
-    ');
-    $stmt->execute([$room['id'], $date]);
-    $previous_day = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
-} else if (isset($room)) {
+if (isset($room)) {
     $room = urldecode($room);
     $stmt = $db->prepare('SELECT id, `name`, joined_at, departed_at FROM rooms WHERE `name` = ?');
     $stmt->execute([$room]);
@@ -57,38 +30,54 @@ if (isset($room, $date)) {
     $room = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
     if (!$room) {
         http_response_code(404);
-        exit("No room found.");
+        exit("Room not found.");
     }
     $room['encoded'] = urlencode($room['name']);
 
-    $stmt = $db->prepare('SELECT YEAR(sent_at) AS msg_year,
-            MONTH(sent_at) AS msg_month,
-            DAY(sent_at) AS msg_day,
-            COUNT(*) AS msg_count
-        FROM messages
-        WHERE room_id = ?
-        GROUP BY YEAR(sent_at), MONTH(sent_at), DAY(sent_at)
-        ORDER BY YEAR(sent_at) DESC, MONTH(sent_at) DESC, DAY(sent_at) DESC
-    ');
-    $stmt->execute([$room['id']]);
-    $raw_dates = $stmt->fetchAll(PDO::FETCH_ASSOC);
-    $dates = [];
-
-    foreach ($raw_dates as $rm) {
-        $y = $rm['msg_year'];
-        $m = $rm['msg_month'];
-        $d = $rm['msg_day'];
-        $c = $rm['msg_count'];
-        if (!array_key_exists($y, $dates)) {
-            $dates[$y] = [];
-        }
-        if (!array_key_exists($m, $dates[$y])) {
-            $dates[$y][$m] = [];
-        }
-        if (!array_key_exists($d, $dates[$y][$m])) {
-            $dates[$y][$m] = [];
+    if (isset($date)) {
+        $user_id = $user ? $user['id'] : "m.user_id";
+
+        $stmt = $db->prepare("SELECT m.id, u.nick, m.command, m.params, m.tags, m.sent_at
+            FROM messages m
+            JOIN rooms r ON r.id = m.room_id
+            JOIN users u ON u.id = m.user_id
+            WHERE m.room_id = ? AND m.user_id = $user_id
+                AND m.sent_at BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY)
+            ORDER BY sent_at DESC
+            LIMIT $limit OFFSET $offset
+        ");
+        $stmt->execute([$room['id'], $date, $date]);
+        $messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
+    } else {
+        $stmt = $db->prepare('SELECT YEAR(sent_at) AS msg_year,
+                MONTH(sent_at) AS msg_month,
+                DAY(sent_at) AS msg_day,
+                COUNT(*) AS msg_count
+            FROM messages
+            WHERE room_id = ?
+            GROUP BY YEAR(sent_at), MONTH(sent_at), DAY(sent_at)
+            ORDER BY YEAR(sent_at) DESC, MONTH(sent_at) DESC, DAY(sent_at) DESC
+        ');
+        $stmt->execute([$room['id']]);
+        $raw_dates = $stmt->fetchAll(PDO::FETCH_ASSOC);
+        $dates = [];
+
+        foreach ($raw_dates as $rm) {
+            $y = $rm['msg_year'];
+            $m = $rm['msg_month'];
+            $d = $rm['msg_day'];
+            $c = $rm['msg_count'];
+            if (!array_key_exists($y, $dates)) {
+                $dates[$y] = [];
+            }
+            if (!array_key_exists($m, $dates[$y])) {
+                $dates[$y][$m] = [];
+            }
+            if (!array_key_exists($d, $dates[$y][$m])) {
+                $dates[$y][$m] = [];
+            }
+            $dates[$y][$m][$d] = $c;
         }
-        $dates[$y][$m][$d] = $c;
     }
 } else {
     $stmt = $db->query('SELECT name FROM rooms ORDER BY joined_at, departed_at DESC');