exec('BEGIN IMMEDIATE'); try { $result = $fn(); $pdo->exec('COMMIT'); return $result; } catch (Throwable $e) { try { $pdo->exec('ROLLBACK'); } catch (Throwable) { /* al afgesloten */ } throw $e; } } function validDate(mixed $value): string { if (!is_string($value) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) { throw new AppException('De datum moet de vorm JJJJ-MM-DD hebben.'); } $dt = DateTimeImmutable::createFromFormat('!Y-m-d', $value); $errors = DateTimeImmutable::getLastErrors(); $bad = !$dt || (is_array($errors) && (($errors['warning_count'] ?? 0) > 0 || ($errors['error_count'] ?? 0) > 0)); if ($bad || $dt->format('Y-m-d') !== $value) throw new AppException('Die datum bestaat niet.'); return $value; } function cleanText(mixed $value, int $max, string $label): string { if (!is_string($value)) throw new AppException($label.' moet tekst zijn.'); $value = trim($value); if ($value === '') throw new AppException($label.' mag niet leeg zijn.'); if (preg_match('/[\x00-\x08\x0B\x0C\x0E-\x1F]/', $value)) throw new AppException($label.' bevat ongeldige tekens.'); if (mb_strlen($value) > $max) throw new AppException($label.' is te lang (maximaal '.$max.' tekens).'); return $value; } function parseStory(mixed $story, int $label): array { if (!is_array($story) || array_is_list($story)) throw new AppException('Verhaal '.$label.' is ongeldig.'); $title = cleanText($story['title'] ?? null, 200, 'De titel van verhaal '.$label); if (!isset($story['paragraphs']) || !is_array($story['paragraphs']) || !array_is_list($story['paragraphs'])) { throw new AppException('Verhaal '.$label.' heeft een lijst alinea\'s nodig.'); } if (count($story['paragraphs']) < 1) throw new AppException('Verhaal '.$label.' heeft minstens één alinea nodig.'); if (count($story['paragraphs']) > 40) throw new AppException('Verhaal '.$label.' heeft te veel alinea\'s (maximaal 40).'); $paragraphs = []; foreach ($story['paragraphs'] as $paragraph) { $paragraphs[] = cleanText($paragraph, 20000, 'Een alinea van verhaal '.$label); } return ['title' => $title, 'paragraphs' => $paragraphs]; } /** @return list}> */ function parseThreeStories(mixed $stories): array { if (!is_array($stories) || !array_is_list($stories)) throw new AppException('Verhalen moeten een lijst zijn.'); if (count($stories) !== 3) throw new AppException('Een volledige dag heeft precies drie verhalen.'); $parsed = []; foreach ($stories as $index => $story) { $one = parseStory($story, $index + 1); $one['position'] = $index + 1; $parsed[] = $one; } return $parsed; } function insertStory(int $dayId, int $position, string $title, array $paragraphs): void { $now = time(); query('INSERT INTO stories (day_id, position, title, created, updated) VALUES (?,?,?,?,?)', [$dayId, $position, $title, $now, $now]); $storyId = (int)db()->lastInsertId(); foreach ($paragraphs as $index => $body) { query('INSERT INTO paragraphs (story_id, position, body) VALUES (?,?,?)', [$storyId, $index + 1, $body]); } } function dayRow(string $date): array { $date = validDate($date); $row = query('SELECT * FROM days WHERE date=?', [$date])->fetch(); if (!$row) throw new AppException('Deze dag bestaat niet.', 404); return $row; } function storyPositions(int $dayId): array { return array_map('intval', query('SELECT position FROM stories WHERE day_id=? ORDER BY position', [$dayId])->fetchAll(PDO::FETCH_COLUMN)); } function loadDay(string $date, ?string $readerId): array { $day = dayRow($date); $stories = query('SELECT * FROM stories WHERE day_id=? ORDER BY position, id', [$day['id']])->fetchAll(); $out = []; foreach ($stories as $story) { $paragraphs = query('SELECT body FROM paragraphs WHERE story_id=? ORDER BY position, id', [$story['id']])->fetchAll(PDO::FETCH_COLUMN); $out[] = [ 'id' => (int)$story['id'], 'position' => (int)$story['position'], 'title' => $story['title'], 'paragraphs' => array_values($paragraphs), ]; } $previous = query('SELECT date FROM days WHERE date < ? ORDER BY date DESC LIMIT 1', [$day['date']])->fetchColumn(); $next = query('SELECT date FROM days WHERE date > ? ORDER BY date ASC LIMIT 1', [$day['date']])->fetchColumn(); $result = [ 'date' => $day['date'], 'title' => $day['title'], 'storyCount' => count($out), 'complete' => count($out) === 3, 'previous' => $previous !== false ? $previous : null, 'next' => $next !== false ? $next : null, 'stories' => $out, ]; if ($readerId !== null) { $result['read'] = (bool)query('SELECT 1 FROM reads WHERE user_id=? AND day_id=?', [$readerId, $day['id']])->fetchColumn(); } return $result; } function listDays(?string $readerId): array { if ($readerId === null) { $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(); } else { $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(); } return array_map(function (array $row) use ($readerId): array { $item = [ 'date' => $row['date'], 'title' => $row['title'], 'storyCount' => (int)$row['story_count'], 'complete' => (int)$row['story_count'] === 3, ]; if ($readerId !== null) $item['read'] = $row['read_at'] !== null; return $item; }, $rows); } function createDay(string $date, mixed $title, mixed $stories): array { $date = validDate($date); $title = cleanText($title, 200, 'De dagtitel'); $parsed = parseThreeStories($stories); transaction(function () use ($date, $title, $parsed): void { if (query('SELECT 1 FROM days WHERE date=?', [$date])->fetchColumn()) { throw new AppException('Er bestaat al een dag op deze datum.', 409); } $now = time(); query('INSERT INTO days (date, title, created, updated) VALUES (?,?,?,?)', [$date, $title, $now, $now]); $dayId = (int)db()->lastInsertId(); foreach ($parsed as $story) insertStory($dayId, $story['position'], $story['title'], $story['paragraphs']); }); return loadDay($date, null); } function updateDay(string $date, array $input): array { $title = array_key_exists('title', $input) ? cleanText($input['title'], 200, 'De dagtitel') : null; $stories = array_key_exists('stories', $input) ? parseThreeStories($input['stories']) : null; if ($title === null && $stories === null) throw new AppException('Geef een titel of precies drie verhalen om bij te werken.'); transaction(function () use ($date, $title, $stories): void { $day = dayRow($date); if ($title !== null) query('UPDATE days SET title=?, updated=? WHERE id=?', [$title, time(), $day['id']]); if ($stories !== null) { query('DELETE FROM stories WHERE day_id=?', [$day['id']]); foreach ($stories as $story) insertStory((int)$day['id'], $story['position'], $story['title'], $story['paragraphs']); query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]); } }); return loadDay($date, null); } function deleteDay(string $date): string { $date = validDate($date); transaction(function () use ($date): void { $day = dayRow($date); query('DELETE FROM days WHERE id=?', [$day['id']]); }); return $date; } function addStory(string $date, array $input): array { transaction(function () use ($date, $input): void { $day = dayRow($date); $used = storyPositions((int)$day['id']); if (count($used) >= 3) throw new AppException('Deze dag heeft al drie verhalen.'); if (array_key_exists('position', $input) && $input['position'] !== null) { if (!is_int($input['position']) && !(is_string($input['position']) && preg_match('/^[123]$/', $input['position']))) { throw new AppException('De plek van een verhaal is 1, 2 of 3.'); } $position = (int)$input['position']; if (in_array($position, $used, true)) throw new AppException('Die plek is al bezet.', 409); } else { $position = [1, 2, 3][0]; foreach ([1, 2, 3] as $candidate) if (!in_array($candidate, $used, true)) { $position = $candidate; break; } } $story = parseStory($input, $position); insertStory((int)$day['id'], $position, $story['title'], $story['paragraphs']); query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]); }); return loadDay($date, null); } function updateStory(string $date, int $position, array $input): array { if ($position < 1 || $position > 3) throw new AppException('De plek van een verhaal is 1, 2 of 3.'); $hasTitle = array_key_exists('title', $input); $hasParagraphs = array_key_exists('paragraphs', $input); if (!$hasTitle && !$hasParagraphs) throw new AppException('Geef een titel of alinea\'s om het verhaal bij te werken.'); $title = $hasTitle ? cleanText($input['title'], 200, 'De titel van verhaal '.$position) : null; $paragraphs = null; if ($hasParagraphs) $paragraphs = parseStory(['title' => $title ?? 'tijdelijk', 'paragraphs' => $input['paragraphs']], $position)['paragraphs']; transaction(function () use ($date, $position, $title, $paragraphs): void { $day = dayRow($date); $story = query('SELECT * FROM stories WHERE day_id=? AND position=?', [$day['id'], $position])->fetch(); if (!$story) throw new AppException('Dit verhaal bestaat niet.', 404); if ($title !== null) query('UPDATE stories SET title=?, updated=? WHERE id=?', [$title, time(), $story['id']]); else query('UPDATE stories SET updated=? WHERE id=?', [time(), $story['id']]); if ($paragraphs !== null) { query('DELETE FROM paragraphs WHERE story_id=?', [$story['id']]); foreach ($paragraphs as $index => $body) query('INSERT INTO paragraphs (story_id, position, body) VALUES (?,?,?)', [$story['id'], $index + 1, $body]); } query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]); }); return loadDay($date, null); } function deleteStory(string $date, int $position): array { if ($position < 1 || $position > 3) throw new AppException('De plek van een verhaal is 1, 2 of 3.'); transaction(function () use ($date, $position): void { $day = dayRow($date); $gone = query('DELETE FROM stories WHERE day_id=? AND position=?', [$day['id'], $position]); if ($gone->rowCount() !== 1) throw new AppException('Dit verhaal bestaat niet.', 404); query('UPDATE days SET updated=? WHERE id=?', [time(), $day['id']]); }); return loadDay($date, null); } function latestWritten(): array { $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(); if (!$row) throw new AppException('Er is nog geen dag geschreven.', 404); return [ 'date' => $row['date'], 'title' => $row['title'], 'storyCount' => (int)$row['story_count'], 'complete' => (int)$row['story_count'] === 3, ]; } function dayGaps(): array { $dates = query('SELECT date FROM days ORDER BY date ASC')->fetchAll(PDO::FETCH_COLUMN); if (!$dates) return ['first' => null, 'latest' => null, 'missing' => [], 'incomplete' => []]; $first = $dates[0]; $latest = $dates[count($dates) - 1]; $known = array_fill_keys($dates, true); $missing = []; $cursor = (new DateTimeImmutable($first))->modify('+1 day'); $end = new DateTimeImmutable($latest); while ($cursor < $end) { $iso = $cursor->format('Y-m-d'); if (!isset($known[$iso])) $missing[] = $iso; $cursor = $cursor->modify('+1 day'); } $incomplete = []; $rows = query('SELECT id, date, title FROM days ORDER BY date ASC')->fetchAll(); foreach ($rows as $row) { $positions = storyPositions((int)$row['id']); if (count($positions) >= 3) continue; $incomplete[] = [ 'date' => $row['date'], 'title' => $row['title'], 'storyCount' => count($positions), 'missingPositions' => array_values(array_diff([1, 2, 3], $positions)), ]; } return ['first' => $first, 'latest' => $latest, 'missing' => $missing, 'incomplete' => $incomplete]; } function weekBounds(string $date): array { $day = new DateTimeImmutable(validDate($date).' 00:00:00 UTC'); $start = $day->modify('-'.((int)$day->format('N') - 1).' days'); return [ 'start' => $start->format('Y-m-d'), 'end' => $start->modify('+6 days')->format('Y-m-d'), 'previous' => $start->modify('-7 days')->format('Y-m-d'), 'next' => $start->modify('+7 days')->format('Y-m-d'), ]; } function weekLabel(string $start, string $end): string { $months = ['januari', 'februari', 'maart', 'april', 'mei', 'juni', 'juli', 'augustus', 'september', 'oktober', 'november', 'december']; $from = new DateTimeImmutable(validDate($start).' 00:00:00 UTC'); $to = new DateTimeImmutable(validDate($end).' 00:00:00 UTC'); $fromDay = $from->format('j'); $toDay = $to->format('j'); $fromMonth = $months[(int)$from->format('n') - 1]; $toMonth = $months[(int)$to->format('n') - 1]; $fromYear = $from->format('Y'); $toYear = $to->format('Y'); if ($fromYear === $toYear && $from->format('n') === $to->format('n')) return $fromDay.'–'.$toDay.' '.$fromMonth.' '.$fromYear; if ($fromYear === $toYear) return $fromDay.' '.$fromMonth.' – '.$toDay.' '.$toMonth.' '.$fromYear; return $fromDay.' '.$fromMonth.' '.$fromYear.' – '.$toDay.' '.$toMonth.' '.$toYear; } function weekAnchor(?string $requestedWeek, ?string $lastRead, ?string $latest, string $today): string { if ($requestedWeek !== null && $requestedWeek !== '') return validDate($requestedWeek); if ($lastRead !== null && $lastRead !== '') return validDate($lastRead); if ($latest !== null && $latest !== '') return validDate($latest); return validDate($today); } function defaultWeekStart(?string $lastRead, ?string $latest, string $today): string { return weekBounds(weekAnchor(null, $lastRead, $latest, $today))['start']; } function daysInWeek(array $days, string $start, string $end): array { $start = validDate($start); $end = validDate($end); $visible = []; foreach ($days as $day) { if (!is_array($day) || !is_string($day['date'] ?? null)) continue; if ($day['date'] >= $start && $day['date'] <= $end) $visible[] = $day; } return $visible; } function weekHasDays(string $start, string $end): bool { return (bool)query('SELECT 1 FROM days WHERE date>=? AND date<=? LIMIT 1', [validDate($start), validDate($end)])->fetchColumn(); } function lastReadDate(string $userId): ?string { $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(); return $date === false ? null : $date; } function overviewWeek(?string $readerId, ?string $requestedWeek, ?string $today = null): array { if ($today === null || $today === '') $today = (new DateTimeImmutable('today', new DateTimeZone('UTC')))->format('Y-m-d'); $lastRead = $readerId !== null ? lastReadDate($readerId) : null; $latest = query('SELECT date FROM days ORDER BY date DESC LIMIT 1')->fetchColumn(); $anchor = weekAnchor($requestedWeek, $lastRead, $latest === false ? null : $latest, $today); $bounds = weekBounds($anchor); $bounds['label'] = weekLabel($bounds['start'], $bounds['end']); $bounds['hasPrevious'] = weekHasDays($bounds['previous'], weekBounds($bounds['previous'])['end']); $bounds['hasNext'] = weekHasDays($bounds['next'], weekBounds($bounds['next'])['end']); return ['lastRead' => $lastRead, 'week' => $bounds]; } function markRead(string $userId, string $date, bool $read): array { $day = dayRow($date); if ($read) { 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()]); } else { query('DELETE FROM reads WHERE user_id=? AND day_id=?', [$userId, $day['id']]); } return loadDay($date, $userId); } function publicUser(string $id): array { $row = query('SELECT id, name, role, theme, font, created FROM users WHERE id=?', [$id])->fetch(); if (!$row) throw new AppException('Gebruiker niet gevonden.', 404); return [ 'id' => $row['id'], 'name' => $row['name'], 'role' => $row['role'], 'admin' => $row['role'] === 'admin', 'theme' => $row['theme'], 'font' => $row['font'], 'created' => (int)$row['created'], ]; } function userBrief(array $row): array { return [ 'id' => $row['id'], 'name' => $row['name'], 'role' => $row['role'], 'admin' => $row['role'] === 'admin', ]; } function listUsers(): array { $rows = query("SELECT id, name, role FROM users ORDER BY role <> 'admin', name COLLATE NOCASE, id")->fetchAll(); return array_map('userBrief', $rows); } function setUserRole(string $actorId, string $targetId, bool $makeAdmin): array { if (!preg_match('/^(?:local:test_user_[12]|auth:[1-9][0-9]{0,17})$/', $targetId)) { throw new AppException('Gebruiker niet gevonden.', 404); } return transaction(function () use ($actorId, $targetId, $makeAdmin): array { $row = query('SELECT id, name, role FROM users WHERE id=?', [$targetId])->fetch(); if (!$row) throw new AppException('Gebruiker niet gevonden.', 404); $next = $makeAdmin ? 'admin' : 'user'; if ($row['role'] === 'admin' && $next === 'user') { if ($actorId === $targetId) throw new AppException('Je kunt je eigen beheerdersrol niet uitzetten.', 403); $admins = (int)query("SELECT COUNT(*) FROM users WHERE role='admin'")->fetchColumn(); if ($admins <= 1) throw new AppException('De laatste beheerder moet beheerder blijven.', 403); } if ($row['role'] !== $next) { query('UPDATE users SET role=? WHERE id=?', [$next, $targetId]); $row['role'] = $next; } return userBrief($row); }); } function saveSettings(string $userId, array $input): array { $theme = $input['theme'] ?? null; $font = $input['font'] ?? null; if (!in_array($theme, ['system', 'light', 'dark'], true)) throw new AppException('Kies systeem, licht of donker.'); if (!in_array($font, ['serif', 'sans', 'rounded'], true)) throw new AppException('Kies serif, schreefloos of rond.'); query('UPDATE users SET theme=?, font=? WHERE id=?', [$theme, $font, $userId]); return publicUser($userId); } function exportUser(string $userId): array { $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(); $keys = query('SELECT id, name, token_prefix, created, revoked FROM api_keys WHERE user_id=? ORDER BY id', [$userId])->fetchAll(); return [ 'exported_at' => gmdate('c'), 'profile' => publicUser($userId), 'reads' => array_map(fn(array $row): array => ['date' => $row['date'], 'title' => $row['title'], 'read_at' => (int)$row['read_at']], $reads), 'api_keys' => array_map(fn(array $row): array => [ 'id' => (int)$row['id'], 'name' => $row['name'], 'prefix' => $row['token_prefix'], 'created' => (int)$row['created'], 'revoked' => $row['revoked'] !== null ? (int)$row['revoked'] : null, ], $keys), ]; } function keyPublic(array $row): array { return [ 'id' => (int)$row['id'], 'name' => $row['name'], 'prefix' => $row['token_prefix'], 'created' => (int)$row['created'], 'revoked' => $row['revoked'] !== null ? (int)$row['revoked'] : null, ]; } function sealToken(string $token): string { $key = hex2bin((string)config()['key_secret']); if ($key === false || strlen($key) !== 32) throw new RuntimeException('key_secret in data/config.php moet 64 hexadecimale tekens zijn.'); $iv = random_bytes(12); $tag = ''; $cipher = openssl_encrypt($token, 'aes-256-gcm', $key, OPENSSL_RAW_DATA, $iv, $tag); if ($cipher === false || strlen($tag) !== 16) throw new RuntimeException('De API-sleutel kon niet worden versleuteld.'); return base64_encode($iv.$tag.$cipher); } function openToken(string $sealed): string { $key = hex2bin((string)config()['key_secret']); $raw = base64_decode($sealed, true); if ($key === false || strlen($key) !== 32 || $raw === false || strlen($raw) < 29) { throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409); } $plain = openssl_decrypt(substr($raw, 28), 'aes-256-gcm', $key, OPENSSL_RAW_DATA, substr($raw, 0, 12), substr($raw, 12, 16)); if (!is_string($plain) || $plain === '') throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409); return $plain; } function createApiKey(string $userId, mixed $name): array { $name = cleanText($name, 80, 'De naam van de sleutel'); $token = 'vb_'.bin2hex(random_bytes(32)); query('INSERT INTO api_keys (user_id, name, token_hash, token_prefix, token_cipher, created, revoked) VALUES (?,?,?,?,?,?,NULL)', [ $userId, $name, hash('sha256', $token), substr($token, 0, 11), sealToken($token), time(), ]); return keyPublic(query('SELECT * FROM api_keys WHERE id=?', [(int)db()->lastInsertId()])->fetch()) + ['token' => $token]; } function revealApiKey(int $id): array { $row = query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch(); if (!$row) throw new AppException('Deze sleutel bestaat niet.', 404); if (!is_string($row['token_cipher'] ?? null) || $row['token_cipher'] === '') { throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409); } $token = openToken($row['token_cipher']); if (!hash_equals($row['token_hash'], hash('sha256', $token))) { throw new AppException('Deze sleutel kan niet opnieuw worden getoond. Maak een nieuwe aan.', 409); } return keyPublic($row) + ['token' => $token]; } function listApiKeys(): array { return array_map('keyPublic', query('SELECT * FROM api_keys ORDER BY revoked IS NOT NULL, id DESC')->fetchAll()); } function revokeApiKey(int $id): array { return transaction(function () use ($id): array { $row = query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch(); if (!$row) throw new AppException('Deze sleutel bestaat niet.', 404); if ($row['revoked'] === null) query('UPDATE api_keys SET revoked=? WHERE id=?', [time(), $id]); return keyPublic(query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch()); }); } function actorFromToken(string $token): array { if (!preg_match('/^vb_[a-f0-9]{64}$/', $token)) throw new AppException('De API-sleutel heeft het verkeerde formaat.', 401, 'format'); $row = query('SELECT * FROM api_keys WHERE token_hash=?', [hash('sha256', $token)])->fetch(); if (!$row) throw new AppException('Deze API-sleutel is onbekend.', 401, 'rejected'); if ($row['revoked'] !== null) throw new AppException('Deze API-sleutel is ingetrokken.', 401, 'rejected'); return ['kind' => 'token', 'userId' => $row['user_id'], 'role' => 'admin', 'keyId' => (int)$row['id']]; }