BlueGit

projects-timfalken-com verhalenboek

Files

verhalenboek / web / lib / Storybook.php

Storybook.php

feat: open het weekoverzicht op de vroegste ongelezen dag
timfa project_87242033_bot_b167a9be22027ad5a4b431d0ad8426c6 · pushed by timfa · 6 weeks ago
62d74362 History
Storybook.php 30.1 KiB Download Raw
1<?php
2declare(strict_types=1);
3
4final class AppException extends RuntimeException {
5 public function __construct(string $message, public int $status = 422, public ?string $auth = null) {
6 parent::__construct($message);
7 }
8}
9
10function transaction(callable $fn): mixed {
11 $pdo = db();
12 $pdo->exec('BEGIN IMMEDIATE');
13 try {
14 $result = $fn();
15 $pdo->exec('COMMIT');
16 return $result;
17 } catch (Throwable $e) {
18 try { $pdo->exec('ROLLBACK'); } catch (Throwable) { /* al afgesloten */ }
19 throw $e;
20 }
21}
22
23function validDate(mixed $value): string {
24 if (!is_string($value) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) {
25 throw new AppException('De datum moet de vorm JJJJ-MM-DD hebben.');
26 }
27 $dt = DateTimeImmutable::createFromFormat('!Y-m-d', $value);
28 $errors = DateTimeImmutable::getLastErrors();
29 $bad = !$dt || (is_array($errors) && (($errors['warning_count'] ?? 0) > 0 || ($errors['error_count'] ?? 0) > 0));
30 if ($bad || $dt->format('Y-m-d') !== $value) throw new AppException('Die datum bestaat niet.');
31 return $value;
32}
33
34function cleanText(mixed $value, int $max, string $label): string {
35 if (!is_string($value)) throw new AppException($label.' moet tekst zijn.');
36 $value = trim($value);
37 if ($value === '') throw new AppException($label.' mag niet leeg zijn.');
38 if (preg_match('/[\x00-\x08\x0B\x0C\x0E-\x1F]/', $value)) throw new AppException($label.' bevat ongeldige tekens.');
39 if (mb_strlen($value) > $max) throw new AppException($label.' is te lang (maximaal '.$max.' tekens).');
40 return $value;
41}
42
43function parseStory(mixed $story, int $label): array {
44 if (!is_array($story) || array_is_list($story)) throw new AppException('Verhaal '.$label.' is ongeldig.');
45 $title = cleanText($story['title'] ?? null, 200, 'De titel van verhaal '.$label);
46 if (!isset($story['paragraphs']) || !is_array($story['paragraphs']) || !array_is_list($story['paragraphs'])) {
47 throw new AppException('Verhaal '.$label.' heeft een lijst alinea\'s nodig.');
48 }
49 if (count($story['paragraphs']) < 1) throw new AppException('Verhaal '.$label.' heeft minstens één alinea nodig.');
50 if (count($story['paragraphs']) > 40) throw new AppException('Verhaal '.$label.' heeft te veel alinea\'s (maximaal 40).');
51 $paragraphs = [];
52 foreach ($story['paragraphs'] as $paragraph) {
53 $paragraphs[] = cleanText($paragraph, 20000, 'Een alinea van verhaal '.$label);
54 }
55 return ['title' => $title, 'paragraphs' => $paragraphs];
56}
57
58/** @return list<array{position:int,title:string,paragraphs:list<string>}> */
59function parseThreeStories(mixed $stories): array {
60 if (!is_array($stories) || !array_is_list($stories)) throw new AppException('Verhalen moeten een lijst zijn.');
61 if (count($stories) !== 3) throw new AppException('Een volledige dag heeft precies drie verhalen.');
62 $parsed = [];
63 foreach ($stories as $index => $story) {
64 $one = parseStory($story, $index + 1);
65 $one['position'] = $index + 1;
66 $parsed[] = $one;
67 }
68 return $parsed;
69}
70
71function insertStory(int $dayId, int $position, string $title, array $paragraphs): void {
72 $now = time();
73 query('INSERT INTO stories (day_id, position, title, created, updated) VALUES (?,?,?,?,?)', [$dayId, $position, $title, $now, $now]);
74 $storyId = (int)db()->lastInsertId();
75 foreach ($paragraphs as $index => $body) {
76 query('INSERT INTO paragraphs (story_id, position, body) VALUES (?,?,?)', [$storyId, $index + 1, $body]);
77 }
78}
79
80function dayRow(string $date): array {
81 $date = validDate($date);
82 $row = query('SELECT * FROM days WHERE date=?', [$date])->fetch();
83 if (!$row) throw new AppException('Deze dag bestaat niet.', 404);
84 return $row;
85}
86
87function storyPositions(int $dayId): array {
88 return array_map('intval', query('SELECT position FROM stories WHERE day_id=? ORDER BY position', [$dayId])->fetchAll(PDO::FETCH_COLUMN));
89}
90
91function loadDay(string $date, ?string $readerId): array {
92 $day = dayRow($date);
93 $stories = query('SELECT * FROM stories WHERE day_id=? ORDER BY position, id', [$day['id']])->fetchAll();
94 $out = [];
95 foreach ($stories as $story) {
96 $paragraphs = query('SELECT body FROM paragraphs WHERE story_id=? ORDER BY position, id', [$story['id']])->fetchAll(PDO::FETCH_COLUMN);
97 $out[] = [
98 'id' => (int)$story['id'],
99 'position' => (int)$story['position'],
100 'title' => $story['title'],
101 'paragraphs' => array_values($paragraphs),
102 ];
103 }
104 $previous = query('SELECT date FROM days WHERE date < ? ORDER BY date DESC LIMIT 1', [$day['date']])->fetchColumn();
105 $next = query('SELECT date FROM days WHERE date > ? ORDER BY date ASC LIMIT 1', [$day['date']])->fetchColumn();
106 $result = [
107 'date' => $day['date'],
108 'title' => $day['title'],
109 'storyCount' => count($out),
110 'complete' => count($out) === 3,
111 'previous' => $previous !== false ? $previous : null,
112 'next' => $next !== false ? $next : null,
113 'stories' => $out,
114 ];
115 if ($readerId !== null) {
116 $result['read'] = (bool)query('SELECT 1 FROM reads WHERE user_id=? AND day_id=?', [$readerId, $day['id']])->fetchColumn();
117 }
118 return $result;
119}
120
121function listDays(?string $readerId): array {
122 if ($readerId === null) {
123 $rows = query('SELECT d.date, d.title, (SELECT COUNT(*) FROM stories s WHERE s.day_id=d.id) AS story_count FROM days d ORDER BY d.date ASC')->fetchAll();
124 } else {
125 $rows = query('SELECT d.date, d.title, (SELECT COUNT(*) FROM stories s WHERE s.day_id=d.id) AS story_count, r.read_at FROM days d LEFT JOIN reads r ON r.day_id=d.id AND r.user_id=? ORDER BY d.date ASC', [$readerId])->fetchAll();
126 }
127 return array_map(function (array $row) use ($readerId): array {
128 $item = [
129 'date' => $row['date'],
130 'title' => $row['title'],
131 'storyCount' => (int)$row['story_count'],
132 'complete' => (int)$row['story_count'] === 3,
133 ];
134 if ($readerId !== null) $item['read'] = $row['read_at'] !== null;
135 return $item;
136 }, $rows);
137}
138
139function createDay(string $date, mixed $title, mixed $stories): array {
140 $date = validDate($date);
141 $title = cleanText($title, 200, 'De dagtitel');
142 $parsed = parseThreeStories($stories);
143 transaction(function () use ($date, $title, $parsed): void {
144 if (query('SELECT 1 FROM days WHERE date=?', [$date])->fetchColumn()) {
145 throw new AppException('Er bestaat al een dag op deze datum.', 409);
146 }
147 $now = time();
148 query('INSERT INTO days (date, title, created, updated) VALUES (?,?,?,?)', [$date, $title, $now, $now]);
149 $dayId = (int)db()->lastInsertId();
150 foreach ($parsed as $story) insertStory($dayId, $story['position'], $story['title'], $story['paragraphs']);
151 });
152 return loadDay($date, null);
153}
154
155function updateDay(string $date, array $input): array {
156 $title = array_key_exists('title', $input) ? cleanText($input['title'], 200, 'De dagtitel') : null;
157 $stories = array_key_exists('stories', $input) ? parseThreeStories($input['stories']) : null;
158 if ($title === null && $stories === null) throw new AppException('Geef een titel of precies drie verhalen om bij te werken.');
159 transaction(function () use ($date, $title, $stories): void {
160 $day = dayRow($date);
161 if ($title !== null) query('UPDATE days SET title=?, updated=? WHERE id=?', [$title, time(), $day['id']]);
162 if ($stories !== null) {
163 query('DELETE FROM stories WHERE day_id=?', [$day['id']]);
164 foreach ($stories as $story) insertStory((int)$day['id'], $story['position'], $story['title'], $story['paragraphs']);
165 query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]);
166 }
167 });
168 return loadDay($date, null);
169}
170
171function deleteDay(string $date): string {
172 $date = validDate($date);
173 transaction(function () use ($date): void {
174 $day = dayRow($date);
175 query('DELETE FROM days WHERE id=?', [$day['id']]);
176 });
177 return $date;
178}
179
180function addStory(string $date, array $input): array {
181 transaction(function () use ($date, $input): void {
182 $day = dayRow($date);
183 $used = storyPositions((int)$day['id']);
184 if (count($used) >= 3) throw new AppException('Deze dag heeft al drie verhalen.');
185 if (array_key_exists('position', $input) && $input['position'] !== null) {
186 if (!is_int($input['position']) && !(is_string($input['position']) && preg_match('/^[123]$/', $input['position']))) {
187 throw new AppException('De plek van een verhaal is 1, 2 of 3.');
188 }
189 $position = (int)$input['position'];
190 if (in_array($position, $used, true)) throw new AppException('Die plek is al bezet.', 409);
191 } else {
192 $position = [1, 2, 3][0];
193 foreach ([1, 2, 3] as $candidate) if (!in_array($candidate, $used, true)) { $position = $candidate; break; }
194 }
195 $story = parseStory($input, $position);
196 insertStory((int)$day['id'], $position, $story['title'], $story['paragraphs']);
197 query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]);
198 });
199 return loadDay($date, null);
200}
201
202function updateStory(string $date, int $position, array $input): array {
203 if ($position < 1 || $position > 3) throw new AppException('De plek van een verhaal is 1, 2 of 3.');
204 $hasTitle = array_key_exists('title', $input);
205 $hasParagraphs = array_key_exists('paragraphs', $input);
206 if (!$hasTitle && !$hasParagraphs) throw new AppException('Geef een titel of alinea\'s om het verhaal bij te werken.');
207 $title = $hasTitle ? cleanText($input['title'], 200, 'De titel van verhaal '.$position) : null;
208 $paragraphs = null;
209 if ($hasParagraphs) $paragraphs = parseStory(['title' => $title ?? 'tijdelijk', 'paragraphs' => $input['paragraphs']], $position)['paragraphs'];
210 transaction(function () use ($date, $position, $title, $paragraphs): void {
211 $day = dayRow($date);
212 $story = query('SELECT * FROM stories WHERE day_id=? AND position=?', [$day['id'], $position])->fetch();
213 if (!$story) throw new AppException('Dit verhaal bestaat niet.', 404);
214 if ($title !== null) query('UPDATE stories SET title=?, updated=? WHERE id=?', [$title, time(), $story['id']]);
215 else query('UPDATE stories SET updated=? WHERE id=?', [time(), $story['id']]);
216 if ($paragraphs !== null) {
217 query('DELETE FROM paragraphs WHERE story_id=?', [$story['id']]);
218 foreach ($paragraphs as $index => $body) query('INSERT INTO paragraphs (story_id, position, body) VALUES (?,?,?)', [$story['id'], $index + 1, $body]);
219 }
220 query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]);
221 });
222 return loadDay($date, null);
223}
224
225function deleteStory(string $date, int $position): array {
226 if ($position < 1 || $position > 3) throw new AppException('De plek van een verhaal is 1, 2 of 3.');
227 transaction(function () use ($date, $position): void {
228 $day = dayRow($date);
229 $gone = query('DELETE FROM stories WHERE day_id=? AND position=?', [$day['id'], $position]);
230 if ($gone->rowCount() !== 1) throw new AppException('Dit verhaal bestaat niet.', 404);
231 query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]);
232 });
233 return loadDay($date, null);
234}
235
236function latestWritten(): array {
237 $row = query('SELECT d.date, d.title, (SELECT COUNT(*) FROM stories s WHERE s.day_id=d.id) AS story_count FROM days d ORDER BY d.date DESC LIMIT 1')->fetch();
238 if (!$row) throw new AppException('Er is nog geen dag geschreven.', 404);
239 return [
240 'date' => $row['date'],
241 'title' => $row['title'],
242 'storyCount' => (int)$row['story_count'],
243 'complete' => (int)$row['story_count'] === 3,
244 ];
245}
246
247function dayGaps(): array {
248 $dates = query('SELECT date FROM days ORDER BY date ASC')->fetchAll(PDO::FETCH_COLUMN);
249 if (!$dates) return ['first' => null, 'latest' => null, 'missing' => [], 'incomplete' => []];
250 $first = $dates[0];
251 $latest = $dates[count($dates) - 1];
252 $known = array_fill_keys($dates, true);
253 $missing = [];
254 $cursor = (new DateTimeImmutable($first))->modify('+1 day');
255 $end = new DateTimeImmutable($latest);
256 while ($cursor < $end) {
257 $iso = $cursor->format('Y-m-d');
258 if (!isset($known[$iso])) $missing[] = $iso;
259 $cursor = $cursor->modify('+1 day');
260 }
261 $incomplete = [];
262 $rows = query('SELECT id, date, title FROM days ORDER BY date ASC')->fetchAll();
263 foreach ($rows as $row) {
264 $positions = storyPositions((int)$row['id']);
265 if (count($positions) >= 3) continue;
266 $incomplete[] = [
267 'date' => $row['date'],
268 'title' => $row['title'],
269 'storyCount' => count($positions),
270 'missingPositions' => array_values(array_diff([1, 2, 3], $positions)),
271 ];
272 }
273 return ['first' => $first, 'latest' => $latest, 'missing' => $missing, 'incomplete' => $incomplete];
274}
275
276function weekBounds(string $date): array {
277 $day = new DateTimeImmutable(validDate($date).' 00:00:00 UTC');
278 $start = $day->modify('-'.((int)$day->format('N') - 1).' days');
279 return [
280 'start' => $start->format('Y-m-d'),
281 'end' => $start->modify('+6 days')->format('Y-m-d'),
282 'previous' => $start->modify('-7 days')->format('Y-m-d'),
283 'next' => $start->modify('+7 days')->format('Y-m-d'),
284 ];
285}
286
287function weekLabel(string $start, string $end): string {
288 $months = ['januari', 'februari', 'maart', 'april', 'mei', 'juni', 'juli', 'augustus', 'september', 'oktober', 'november', 'december'];
289 $from = new DateTimeImmutable(validDate($start).' 00:00:00 UTC');
290 $to = new DateTimeImmutable(validDate($end).' 00:00:00 UTC');
291 $fromDay = $from->format('j');
292 $toDay = $to->format('j');
293 $fromMonth = $months[(int)$from->format('n') - 1];
294 $toMonth = $months[(int)$to->format('n') - 1];
295 $fromYear = $from->format('Y');
296 $toYear = $to->format('Y');
297 if ($fromYear === $toYear && $from->format('n') === $to->format('n')) return $fromDay.'–'.$toDay.' '.$fromMonth.' '.$fromYear;
298 if ($fromYear === $toYear) return $fromDay.' '.$fromMonth.' – '.$toDay.' '.$toMonth.' '.$fromYear;
299 return $fromDay.' '.$fromMonth.' '.$fromYear.' – '.$toDay.' '.$toMonth.' '.$toYear;
300}
301
302function weekAnchor(?string $requestedWeek, ?string $oldestUnread, ?string $lastRead, ?string $latest, string $today): string {
303 if ($requestedWeek !== null && $requestedWeek !== '') return validDate($requestedWeek);
304 if ($oldestUnread !== null && $oldestUnread !== '') return validDate($oldestUnread);
305 if ($lastRead !== null && $lastRead !== '') return validDate($lastRead);
306 if ($latest !== null && $latest !== '') return validDate($latest);
307 return validDate($today);
308}
309
310function defaultWeekStart(?string $oldestUnread, ?string $lastRead, ?string $latest, string $today): string {
311 return weekBounds(weekAnchor(null, $oldestUnread, $lastRead, $latest, $today))['start'];
312}
313
314function daysInWeek(array $days, string $start, string $end): array {
315 $start = validDate($start);
316 $end = validDate($end);
317 $visible = [];
318 foreach ($days as $day) {
319 if (!is_array($day) || !is_string($day['date'] ?? null)) continue;
320 if ($day['date'] >= $start && $day['date'] <= $end) $visible[] = $day;
321 }
322 return $visible;
323}
324
325function weekHasDays(string $start, string $end): bool {
326 return (bool)query('SELECT 1 FROM days WHERE date>=? AND date<=? LIMIT 1', [validDate($start), validDate($end)])->fetchColumn();
327}
328
329function lastReadDate(string $userId): ?string {
330 $date = query('SELECT d.date FROM reads r JOIN days d ON d.id=r.day_id WHERE r.user_id=? ORDER BY r.read_at DESC, d.date DESC LIMIT 1', [$userId])->fetchColumn();
331 return $date === false ? null : $date;
332}
333
334/** Vroegste dagrecord zonder leesregel voor deze lezer. Een dag zonder verhalen telt mee. */
335function oldestUnreadDate(string $userId): ?string {
336 $date = query('SELECT d.date FROM days d LEFT JOIN reads r ON r.day_id=d.id AND r.user_id=? WHERE r.user_id IS NULL ORDER BY d.date ASC LIMIT 1', [$userId])->fetchColumn();
337 return $date === false ? null : $date;
338}
339
340function overviewWeek(?string $readerId, ?string $requestedWeek, ?string $today = null): array {
341 if ($today === null || $today === '') $today = (new DateTimeImmutable('today', new DateTimeZone('UTC')))->format('Y-m-d');
342 $lastRead = $readerId !== null ? lastReadDate($readerId) : null;
343 $oldestUnread = $readerId !== null ? oldestUnreadDate($readerId) : null;
344 $latest = query('SELECT date FROM days ORDER BY date DESC LIMIT 1')->fetchColumn();
345 $anchor = weekAnchor($requestedWeek, $oldestUnread, $lastRead, $latest === false ? null : $latest, $today);
346 $bounds = weekBounds($anchor);
347 $bounds['label'] = weekLabel($bounds['start'], $bounds['end']);
348 $bounds['hasPrevious'] = weekHasDays($bounds['previous'], weekBounds($bounds['previous'])['end']);
349 $bounds['hasNext'] = weekHasDays($bounds['next'], weekBounds($bounds['next'])['end']);
350 return ['lastRead' => $lastRead, 'week' => $bounds];
351}
352
353function knownUserId(mixed $id): bool {
354 return is_string($id) && preg_match('/^(?:local:test_user_[12]|auth:[1-9][0-9]{0,17})$/', $id) === 1;
355}
356
357/** Alleen profielfoto's van auth.timfalken.com. Een relatief /avatar/{id}-pad wordt absoluut. */
358function normalizeAvatarUrl(mixed $value): ?string {
359 if (!is_string($value)) return null;
360 $value = trim($value);
361 if ($value === '' || strlen($value) > 300) return null;
362 $query = '';
363 if (str_starts_with($value, '/')) {
364 $split = strpos($value, '?');
365 if ($split === false) {
366 $path = $value;
367 } else {
368 $path = substr($value, 0, $split);
369 $query = substr($value, $split);
370 }
371 } else {
372 $parts = parse_url($value);
373 if (!is_array($parts) || ($parts['scheme'] ?? '') !== 'https' || strcasecmp((string)($parts['host'] ?? ''), 'auth.timfalken.com') !== 0) return null;
374 if (isset($parts['user']) || isset($parts['pass']) || isset($parts['port'])) return null;
375 $path = (string)($parts['path'] ?? '');
376 if (isset($parts['query'])) $query = '?'.$parts['query'];
377 }
378 if (!preg_match('#^/avatar/([1-9][0-9]{0,17})$#', $path, $match)) return null;
379 if ($query !== '' && !preg_match('/^\?[A-Za-z0-9._~%=&-]{1,200}$/', $query)) return null;
380 return 'https://auth.timfalken.com/avatar/'.$match[1].$query;
381}
382
383/** @param array<string,mixed> $payload @return list<array{id:string,name:string,avatar_url:?string}> */
384function parseAuthFriends(array $payload): array {
385 $list = $payload['friends'] ?? null;
386 if (!is_array($list)) return [];
387 $friends = [];
388 foreach ($list as $friend) {
389 if (!is_array($friend)) continue;
390 $rawId = $friend['id'] ?? null;
391 $numeric = is_int($rawId) || (is_string($rawId) && preg_match('/^[1-9][0-9]{0,17}$/', $rawId));
392 if (!$numeric || (int)$rawId < 1) continue;
393 $id = 'auth:'.(int)$rawId;
394 $name = $friend['username'] ?? null;
395 if (!is_string($name)) continue;
396 $name = trim($name);
397 if ($name === '' || mb_strlen($name) > 64 || preg_match('/[\x00-\x1F]/', $name)) continue;
398 $friends[$id] = [
399 'id' => $id,
400 'name' => $name,
401 'avatar_url' => normalizeAvatarUrl($friend['avatar_url'] ?? null),
402 ];
403 }
404 return array_values($friends);
405}
406
407/** @return list<array{id:string,name:string,avatar_url:?string}> */
408function localFriendProfiles(string $exceptUserId): array {
409 $rows = query('SELECT id, name FROM users WHERE id <> ? ORDER BY name COLLATE NOCASE, id', [$exceptUserId])->fetchAll();
410 $friends = [];
411 foreach ($rows as $row) {
412 if (!knownUserId($row['id'] ?? null) || !is_string($row['name'] ?? null) || $row['name'] === '') continue;
413 $friends[] = ['id' => $row['id'], 'name' => $row['name'], 'avatar_url' => null];
414 }
415 return $friends;
416}
417
418/** @return list<array{id:string,name:string,avatar_url:?string}> */
419function authFriendProfiles(): array {
420 $cachedAt = $_SESSION['friends_cached_at'] ?? 0;
421 $cached = $_SESSION['friends_cache'] ?? null;
422 if (is_int($cachedAt) && is_array($cached) && (time() - $cachedAt) < 60) return $cached;
423 $token = $_SESSION['token'] ?? null;
424 if (!is_string($token) || $token === '') return [];
425 try {
426 $friends = parseAuthFriends(authRequest('/api/friends', null, $token));
427 } catch (Throwable $e) {
428 error_log('Vriendenlijst niet bereikbaar.');
429 return [];
430 }
431 $_SESSION['friends_cache'] = $friends;
432 $_SESSION['friends_cached_at'] = time();
433 return $friends;
434}
435
436/** @return list<array{id:string,name:string,avatar_url:?string}> */
437function sessionFriends(string $userId): array {
438 $friends = localDev() ? localFriendProfiles($userId) : authFriendProfiles();
439 return array_values(array_filter($friends, fn(array $friend): bool => $friend['id'] !== $userId));
440}
441
442/**
443 * Vrienden die een dag gelezen hebben, per datum.
444 * Maximaal vijf, de recentste leeshandeling eerst. Bij een gelijk moment telt de naam, daarna het id.
445 *
446 * @param list<array{id:string,name:string,avatar_url:?string}> $friends
447 * @return array<string, list<array{id:string,name:string,avatar_url:?string}>>
448 */
449function friendReadsByDay(array $friends): array {
450 $byId = [];
451 foreach ($friends as $friend) {
452 if (!is_array($friend) || !knownUserId($friend['id'] ?? null)) continue;
453 $name = $friend['name'] ?? '';
454 if (!is_string($name) || $name === '') continue;
455 $byId[$friend['id']] = [
456 'id' => $friend['id'],
457 'name' => $name,
458 'avatar_url' => normalizeAvatarUrl($friend['avatar_url'] ?? null),
459 ];
460 }
461 if ($byId === []) return [];
462 $ids = array_keys($byId);
463 $placeholders = implode(',', array_fill(0, count($ids), '?'));
464 $rows = query(
465 'SELECT d.date AS date, r.user_id AS user_id, r.read_at AS read_at, u.name AS stored_name
466 FROM reads r
467 JOIN days d ON d.id = r.day_id
468 JOIN users u ON u.id = r.user_id
469 WHERE r.user_id IN ('.$placeholders.')
470 ORDER BY d.date ASC, r.read_at DESC, u.name COLLATE NOCASE ASC, r.user_id ASC',
471 $ids
472 )->fetchAll();
473 $days = [];
474 foreach ($rows as $row) {
475 $date = $row['date'];
476 if (!isset($days[$date])) $days[$date] = [];
477 if (count($days[$date]) >= 5) continue;
478 $friend = $byId[$row['user_id']];
479 $stored = $row['stored_name'];
480 $days[$date][] = [
481 'id' => $friend['id'],
482 'name' => is_string($stored) && $stored !== '' ? $stored : $friend['name'],
483 'avatar_url' => $friend['avatar_url'],
484 ];
485 }
486 return $days;
487}
488
489function markRead(string $userId, string $date, bool $read): array {
490 $day = dayRow($date);
491 if ($read) {
492 query('INSERT INTO reads (user_id, day_id, read_at) VALUES (?,?,?) ON CONFLICT(user_id, day_id) DO UPDATE SET read_at=excluded.read_at', [$userId, $day['id'], time()]);
493 } else {
494 query('DELETE FROM reads WHERE user_id=? AND day_id=?', [$userId, $day['id']]);
495 }
496 return loadDay($date, $userId);
497}
498
499function publicUser(string $id): array {
500 $row = query('SELECT id, name, role, theme, font, created FROM users WHERE id=?', [$id])->fetch();
501 if (!$row) throw new AppException('Gebruiker niet gevonden.', 404);
502 return [
503 'id' => $row['id'],
504 'name' => $row['name'],
505 'role' => $row['role'],
506 'admin' => $row['role'] === 'admin',
507 'theme' => $row['theme'],
508 'font' => $row['font'],
509 'created' => (int)$row['created'],
510 ];
511}
512
513function userBrief(array $row): array {
514 return [
515 'id' => $row['id'],
516 'name' => $row['name'],
517 'role' => $row['role'],
518 'admin' => $row['role'] === 'admin',
519 ];
520}
521
522function listUsers(): array {
523 $rows = query("SELECT id, name, role FROM users ORDER BY role <> 'admin', name COLLATE NOCASE, id")->fetchAll();
524 return array_map('userBrief', $rows);
525}
526
527function setUserRole(string $actorId, string $targetId, bool $makeAdmin): array {
528 if (!preg_match('/^(?:local:test_user_[12]|auth:[1-9][0-9]{0,17})$/', $targetId)) {
529 throw new AppException('Gebruiker niet gevonden.', 404);
530 }
531 return transaction(function () use ($actorId, $targetId, $makeAdmin): array {
532 $row = query('SELECT id, name, role FROM users WHERE id=?', [$targetId])->fetch();
533 if (!$row) throw new AppException('Gebruiker niet gevonden.', 404);
534 $next = $makeAdmin ? 'admin' : 'user';
535 if ($row['role'] === 'admin' && $next === 'user') {
536 if ($actorId === $targetId) throw new AppException('Je kunt je eigen beheerdersrol niet uitzetten.', 403);
537 $admins = (int)query("SELECT COUNT(*) FROM users WHERE role='admin'")->fetchColumn();
538 if ($admins <= 1) throw new AppException('De laatste beheerder moet beheerder blijven.', 403);
539 }
540 if ($row['role'] !== $next) {
541 query('UPDATE users SET role=? WHERE id=?', [$next, $targetId]);
542 $row['role'] = $next;
543 }
544 return userBrief($row);
545 });
546}
547
548function saveSettings(string $userId, array $input): array {
549 $theme = $input['theme'] ?? null;
550 $font = $input['font'] ?? null;
551 if (!in_array($theme, ['system', 'light', 'dark'], true)) throw new AppException('Kies systeem, licht of donker.');
552 if (!in_array($font, ['serif', 'sans', 'rounded'], true)) throw new AppException('Kies serif, schreefloos of rond.');
553 query('UPDATE users SET theme=?, font=? WHERE id=?', [$theme, $font, $userId]);
554 return publicUser($userId);
555}
556
557function exportUser(string $userId): array {
558 $reads = query('SELECT d.date, d.title, r.read_at FROM reads r JOIN days d ON d.id=r.day_id WHERE r.user_id=? ORDER BY d.date', [$userId])->fetchAll();
559 $keys = query('SELECT id, name, token_prefix, created, revoked FROM api_keys WHERE user_id=? ORDER BY id', [$userId])->fetchAll();
560 return [
561 'exported_at' => gmdate('c'),
562 'profile' => publicUser($userId),
563 'reads' => array_map(fn(array $row): array => ['date' => $row['date'], 'title' => $row['title'], 'read_at' => (int)$row['read_at']], $reads),
564 'api_keys' => array_map(fn(array $row): array => [
565 'id' => (int)$row['id'],
566 'name' => $row['name'],
567 'prefix' => $row['token_prefix'],
568 'created' => (int)$row['created'],
569 'revoked' => $row['revoked'] !== null ? (int)$row['revoked'] : null,
570 ], $keys),
571 ];
572}
573
574function keyPublic(array $row): array {
575 return [
576 'id' => (int)$row['id'],
577 'name' => $row['name'],
578 'prefix' => $row['token_prefix'],
579 'created' => (int)$row['created'],
580 'revoked' => $row['revoked'] !== null ? (int)$row['revoked'] : null,
581 ];
582}
583
584function sealToken(string $token): string {
585 $key = hex2bin((string)config()['key_secret']);
586 if ($key === false || strlen($key) !== 32) throw new RuntimeException('key_secret in data/config.php moet 64 hexadecimale tekens zijn.');
587 $iv = random_bytes(12);
588 $tag = '';
589 $cipher = openssl_encrypt($token, 'aes-256-gcm', $key, OPENSSL_RAW_DATA, $iv, $tag);
590 if ($cipher === false || strlen($tag) !== 16) throw new RuntimeException('De API-sleutel kon niet worden versleuteld.');
591 return base64_encode($iv.$tag.$cipher);
592}
593
594function openToken(string $sealed): string {
595 $key = hex2bin((string)config()['key_secret']);
596 $raw = base64_decode($sealed, true);
597 if ($key === false || strlen($key) !== 32 || $raw === false || strlen($raw) < 29) {
598 throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409);
599 }
600 $plain = openssl_decrypt(substr($raw, 28), 'aes-256-gcm', $key, OPENSSL_RAW_DATA, substr($raw, 0, 12), substr($raw, 12, 16));
601 if (!is_string($plain) || $plain === '') throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409);
602 return $plain;
603}
604
605function createApiKey(string $userId, mixed $name): array {
606 $name = cleanText($name, 80, 'De naam van de sleutel');
607 $token = 'vb_'.bin2hex(random_bytes(32));
608 query('INSERT INTO api_keys (user_id, name, token_hash, token_prefix, token_cipher, created, revoked) VALUES (?,?,?,?,?,?,NULL)', [
609 $userId, $name, hash('sha256', $token), substr($token, 0, 11), sealToken($token), time(),
610 ]);
611 return keyPublic(query('SELECT * FROM api_keys WHERE id=?', [(int)db()->lastInsertId()])->fetch()) + ['token' => $token];
612}
613
614function revealApiKey(int $id): array {
615 $row = query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch();
616 if (!$row) throw new AppException('Deze sleutel bestaat niet.', 404);
617 if (!is_string($row['token_cipher'] ?? null) || $row['token_cipher'] === '') {
618 throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409);
619 }
620 $token = openToken($row['token_cipher']);
621 if (!hash_equals($row['token_hash'], hash('sha256', $token))) {
622 throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409);
623 }
624 return keyPublic($row) + ['token' => $token];
625}
626
627function listApiKeys(): array {
628 return array_map('keyPublic', query('SELECT * FROM api_keys ORDER BY revoked IS NOT NULL, id DESC')->fetchAll());
629}
630
631function revokeApiKey(int $id): array {
632 return transaction(function () use ($id): array {
633 $row = query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch();
634 if (!$row) throw new AppException('Deze sleutel bestaat niet.', 404);
635 if ($row['revoked'] === null) query('UPDATE api_keys SET revoked=? WHERE id=?', [time(), $id]);
636 return keyPublic(query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch());
637 });
638}
639
640function actorFromToken(string $token): array {
641 if (!preg_match('/^vb_[a-f0-9]{64}$/', $token)) throw new AppException('De API-sleutel heeft het verkeerde formaat.', 401, 'format');
642 $row = query('SELECT * FROM api_keys WHERE token_hash=?', [hash('sha256', $token)])->fetch();
643 if (!$row) throw new AppException('Deze API-sleutel is onbekend.', 401, 'rejected');
644 if ($row['revoked'] !== null) throw new AppException('Deze API-sleutel is ingetrokken.', 401, 'rejected');
645 return ['kind' => 'token', 'userId' => $row['user_id'], 'role' => 'admin', 'keyId' => (int)$row['id']];
646}