From 1a7db44117f906cd549706761d8b40e12be18985 Mon Sep 17 00:00:00 2001
From: ilotterytea <iltsu@alright.party>
Date: Tue, 5 Aug 2025 15:43:23 +0500
Subject: feat: search messages by user

---
 web/index.php | 87 ++++++++++++++++++++++++++++++++++-------------------------
 1 file changed, 50 insertions(+), 37 deletions(-)

diff --git a/web/index.php b/web/index.php
index b722095..7e28e06 100644
--- a/web/index.php
+++ b/web/index.php
@@ -33,53 +33,66 @@ if (isset($room)) {
         exit("Room not found.");
     }
     $room['encoded'] = urlencode($room['name']);
+}
 
-    if (isset($date)) {
-        $user_id = $user ? $user['id'] : "m.user_id";
+if (isset($date)) {
+    $user_id = $user ? $user['id'] : "m.user_id";
+    $room_id = $room ? $room['id'] : "m.room_id";
 
-        $stmt = $db->prepare("SELECT m.id, u.nick, m.command, m.params, m.tags, m.sent_at
+    $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
+            WHERE m.room_id = $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,
+    $stmt->execute([$date, $date]);
+    $messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
+} else if ($room || $user) {
+    $room_id = isset($room) ? $room['id'] : 'room_id';
+    $user_id = isset($user) ? $user['id'] : 'user_id';
+    $stmt = $db->query("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 = ?
+            WHERE room_id = $room_id AND user_id = $user_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;
+        ");
+    $stmt->execute();
+    $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;
     }
-} else {
+}
+
+$page_title = (isset($user) ? ($user['nick'] . (isset($room) ? ' in ' : '')) : '') . (isset($room) ? "{$room['name']}" : '');
+$link = match (true) {
+    isset($user, $room) => "r={$room['encoded']}&u={$user['nick']}",
+    isset($user) => "u={$user['nick']}",
+    isset($room) => "r={$room['encoded']}",
+    default => ""
+};
+
+if (!isset($user) && !isset($room)) {
     $stmt = $db->query('SELECT name FROM rooms ORDER BY joined_at, departed_at DESC');
     $stmt->execute();
     $rooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
@@ -101,8 +114,8 @@ if (isset($room)) {
 </head>
 
 <body>
-    <?php if (isset($room, $date)): ?>
-        <h1><?= sprintf('Log for %s, %s', $room['name'], $date) ?></h1>
+    <?php if (isset($messages)): ?>
+        <h1><?= sprintf('Log for %s, %s', $page_title, $date) ?></h1>
         <?php if (empty($messages)): ?>
             <p>No messages.</p>
         <?php else: ?>
@@ -116,7 +129,7 @@ if (isset($room)) {
                 <?php foreach ($messages as $m): ?>
                     <tr class="message" data-irc-tags="<?= $m['tags'] ?>" id="msgid=<?= $m['id'] ?>">
                         <td class="datetime"><a
-                                href="/?r=<?= urlencode($room['name']) ?>&d=<?= urlencode($date) ?>#msgid=<?= $m['id'] ?>"><?= date('H:i', strtotime($m['sent_at'])) ?></a>
+                                href="/?<?= $link ?>&d=<?= urlencode($date) ?>#msgid=<?= $m['id'] ?>"><?= date('H:i', strtotime($m['sent_at'])) ?></a>
                         </td>
                         <td class="nick">
                             <?= (!isset($prev_msg_nick) || $prev_msg_nick != $m['nick']) ? $m['nick'] : '' ?>
@@ -127,8 +140,8 @@ if (isset($room)) {
                 <?php endforeach; ?>
             </table>
         <?php endif; ?>
-    <?php elseif (isset($room)): ?>
-        <h1><a href="/"><?= INSTANCE_NAME ?></a> - Index of <?= $room['name'] ?></h1>
+    <?php elseif (isset($dates)): ?>
+        <h1><a href="/"><?= INSTANCE_NAME ?></a> - Index of <?= $page_title ?></h1>
         <div class="calendar-wrapper">
             <?php foreach ($dates as $year => $months): ?>
                 <?php foreach ($months as $month => $days): ?>
@@ -157,7 +170,7 @@ if (isset($room)) {
                             for ($day = 1; $day <= $dm; $day++) {
                                 echo '<td>';
                                 if (array_key_exists($day, $days) && $days[$day] > 0) {
-                                    echo "<a href='/?r={$room['encoded']}&d=$year-$month-$day'>$day</a>";
+                                    echo "<a href='/?$link&d=$year-$month-$day'>$day</a>";
                                 } else {
                                     echo $day;
                                 }
@@ -196,7 +209,7 @@ if (isset($room)) {
     <?php endif; ?>
 </body>
 
-<?php if (isset($room, $date)): ?>
+<?php if (isset($messages)): ?>
     <script>
         const messages = document.querySelectorAll(".message");
         for (const m of messages) {
-- 
cgit v1.2.3