| 1 | <?php |
| 2 | declare(strict_types=1); |
| 3 | |
| 4 | final class AppException extends RuntimeException { |
| 5 | public function __construct(string $message, public int $status = 422) { |
| 6 | parent::__construct($message); |
| 7 | } |
| 8 | } |
| 9 | |
| 10 | function 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 | |
| 23 | function 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 | |
| 34 | function 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 | |
| 43 | function 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>}> */ |
| 59 | function 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 | |
| 71 | function 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 | |
| 80 | function 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 | |
| 87 | function 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 | |
| 91 | function 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 | |
| 121 | function 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 | |
| 139 | function 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 | |
| 155 | function 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 | |
| 171 | function 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 | |
| 180 | function 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 | |
| 202 | function 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 | |
| 225 | function 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 | |
| 236 | function 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 | |
| 247 | function 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 | |
| 276 | function markRead(string $userId, string $date, bool $read): array { |
| 277 | $day = dayRow($date); |
| 278 | if ($read) { |
| 279 | 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()]); |
| 280 | } else { |
| 281 | query('DELETE FROM reads WHERE user_id=? AND day_id=?', [$userId, $day['id']]); |
| 282 | } |
| 283 | return loadDay($date, $userId); |
| 284 | } |
| 285 | |
| 286 | function publicUser(string $id): array { |
| 287 | $row = query('SELECT id, name, role, theme, font, created FROM users WHERE id=?', [$id])->fetch(); |
| 288 | if (!$row) throw new AppException('Gebruiker niet gevonden.', 404); |
| 289 | return [ |
| 290 | 'id' => $row['id'], |
| 291 | 'name' => $row['name'], |
| 292 | 'role' => $row['role'], |
| 293 | 'admin' => $row['role'] === 'admin', |
| 294 | 'theme' => $row['theme'], |
| 295 | 'font' => $row['font'], |
| 296 | 'created' => (int)$row['created'], |
| 297 | ]; |
| 298 | } |
| 299 | |
| 300 | function saveSettings(string $userId, array $input): array { |
| 301 | $theme = $input['theme'] ?? null; |
| 302 | $font = $input['font'] ?? null; |
| 303 | if (!in_array($theme, ['system', 'light', 'dark'], true)) throw new AppException('Kies systeem, licht of donker.'); |
| 304 | if (!in_array($font, ['serif', 'sans', 'rounded'], true)) throw new AppException('Kies serif, schreefloos of rond.'); |
| 305 | query('UPDATE users SET theme=?, font=? WHERE id=?', [$theme, $font, $userId]); |
| 306 | return publicUser($userId); |
| 307 | } |
| 308 | |
| 309 | function exportUser(string $userId): array { |
| 310 | $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(); |
| 311 | $keys = query('SELECT id, name, token_prefix, created, revoked FROM api_keys WHERE user_id=? ORDER BY id', [$userId])->fetchAll(); |
| 312 | return [ |
| 313 | 'exported_at' => gmdate('c'), |
| 314 | 'profile' => publicUser($userId), |
| 315 | 'reads' => array_map(fn(array $row): array => ['date' => $row['date'], 'title' => $row['title'], 'read_at' => (int)$row['read_at']], $reads), |
| 316 | 'api_keys' => array_map(fn(array $row): array => [ |
| 317 | 'id' => (int)$row['id'], |
| 318 | 'name' => $row['name'], |
| 319 | 'prefix' => $row['token_prefix'], |
| 320 | 'created' => (int)$row['created'], |
| 321 | 'revoked' => $row['revoked'] !== null ? (int)$row['revoked'] : null, |
| 322 | ], $keys), |
| 323 | ]; |
| 324 | } |
| 325 | |
| 326 | function keyPublic(array $row): array { |
| 327 | return [ |
| 328 | 'id' => (int)$row['id'], |
| 329 | 'name' => $row['name'], |
| 330 | 'prefix' => $row['token_prefix'], |
| 331 | 'created' => (int)$row['created'], |
| 332 | 'revoked' => $row['revoked'] !== null ? (int)$row['revoked'] : null, |
| 333 | ]; |
| 334 | } |
| 335 | |
| 336 | function createApiKey(string $userId, mixed $name): array { |
| 337 | $name = cleanText($name, 80, 'De naam van de sleutel'); |
| 338 | $token = 'vb_'.bin2hex(random_bytes(32)); |
| 339 | query('INSERT INTO api_keys (user_id, name, token_hash, token_prefix, created, revoked) VALUES (?,?,?,?,?,NULL)', [ |
| 340 | $userId, $name, hash('sha256', $token), substr($token, 0, 11), time(), |
| 341 | ]); |
| 342 | return keyPublic(query('SELECT * FROM api_keys WHERE id=?', [(int)db()->lastInsertId()])->fetch()) + ['token' => $token]; |
| 343 | } |
| 344 | |
| 345 | function listApiKeys(): array { |
| 346 | return array_map('keyPublic', query('SELECT * FROM api_keys ORDER BY revoked IS NOT NULL, id DESC')->fetchAll()); |
| 347 | } |
| 348 | |
| 349 | function revokeApiKey(int $id): array { |
| 350 | return transaction(function () use ($id): array { |
| 351 | $row = query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch(); |
| 352 | if (!$row) throw new AppException('Deze sleutel bestaat niet.', 404); |
| 353 | if ($row['revoked'] === null) query('UPDATE api_keys SET revoked=? WHERE id=?', [time(), $id]); |
| 354 | return keyPublic(query('SELECT * FROM api_keys WHERE id=?', [$id])->fetch()); |
| 355 | }); |
| 356 | } |
| 357 | |
| 358 | function actorFromToken(string $token): array { |
| 359 | if (!preg_match('/^vb_[a-f0-9]{64}$/', $token)) throw new AppException('Ongeldige API-sleutel.', 401); |
| 360 | $row = query('SELECT * FROM api_keys WHERE token_hash=?', [hash('sha256', $token)])->fetch(); |
| 361 | if (!$row) throw new AppException('Ongeldige API-sleutel.', 401); |
| 362 | if ($row['revoked'] !== null) throw new AppException('Deze API-sleutel is ingetrokken.', 401); |
| 363 | return ['kind' => 'token', 'userId' => $row['user_id'], 'role' => 'admin', 'keyId' => (int)$row['id']]; |
| 364 | } |
projects-timfalken-com verhalenboek