1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507

package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"regexp"
	"strings"
	"time"
)

// Block kinds. A block's kind decides how its payload columns are read and
// which renderer runs over it.
const (
	KindMarkdown = "markdown"
	KindCode     = "code"
	KindImage    = "image"
	KindSTL      = "stl"
)

// validKinds gates what the HTTP layer is allowed to write into block.kind, so
// a typo in a request cannot create a block that no renderer knows about.
var validKinds = map[string]bool{
	KindMarkdown: true,
	KindCode:     true,
	KindImage:    true,
	KindSTL:      true,
}

// Post is one authored document.
type Post struct {
	ID          int64
	Slug        string
	Title       string
	Subtitle    string
	Status      string
	CreatedAt   time.Time
	UpdatedAt   time.Time
	PublishedAt *time.Time
}

// Block is one entry in a post's ordered list of contents.
type Block struct {
	ID       int64
	PostID   int64
	Position int
	Kind     string
	Content  string
	AssetID  *int64
	Meta     BlockMeta

	// Asset is populated for blocks that reference one, so a page render does
	// not need a second query per block.
	Asset *Asset
}

// BlockMeta is the per-kind extra data, stored as JSON in block.meta. Every
// field is optional, which is what lets a new block kind add its own knobs
// without a schema migration.
type BlockMeta struct {
	// Language is the chroma lexer name for code blocks. Empty means "guess".
	Language string `json:"language,omitempty"`
	// Alt is the image alt text — the accessible description, not a caption.
	Alt string `json:"alt,omitempty"`
	// Caption is shown beneath an image or model as a <figcaption>.
	Caption string `json:"caption,omitempty"`
}

// Asset is an uploaded file plus the dimensions we derived from it.
type Asset struct {
	ID        int64
	SHA256    string
	Kind      string
	MIME      string
	Filename  string
	Width     int
	Height    int
	CreatedAt time.Time
}

// ErrNotFound is returned when a lookup by id or slug matches no row.
var ErrNotFound = errors.New("not found")

// ---------------------------------------------------------------------------
// Posts

func createPost(db *sql.DB, title string) (*Post, error) {
	now := time.Now()
	slug, err := uniqueSlug(db, slugify(title))
	if err != nil {
		return nil, err
	}
	res, err := db.Exec(
		`INSERT INTO post (slug, title, created_at, updated_at) VALUES (?, ?, ?, ?)`,
		slug, title, now.Unix(), now.Unix())
	if err != nil {
		return nil, fmt.Errorf("insert post: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("post id: %w", err)
	}
	return &Post{ID: id, Slug: slug, Title: title, Status: "draft", CreatedAt: now, UpdatedAt: now}, nil
}

func listPosts(db *sql.DB) ([]Post, error) {
	rows, err := db.Query(
		`SELECT id, slug, title, subtitle, status, created_at, updated_at, published_at
		   FROM post ORDER BY updated_at DESC`)
	if err != nil {
		return nil, fmt.Errorf("list posts: %w", err)
	}
	defer rows.Close()
	var out []Post
	for rows.Next() {
		p, err := scanPost(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *p)
	}
	return out, rows.Err()
}

func postBySlug(db *sql.DB, slug string) (*Post, error) {
	row := db.QueryRow(
		`SELECT id, slug, title, subtitle, status, created_at, updated_at, published_at
		   FROM post WHERE slug = ?`, slug)
	return scanPost(row)
}

func postByID(db *sql.DB, id int64) (*Post, error) {
	row := db.QueryRow(
		`SELECT id, slug, title, subtitle, status, created_at, updated_at, published_at
		   FROM post WHERE id = ?`, id)
	return scanPost(row)
}

// scanner is the row-scanning subset shared by *sql.Row and *sql.Rows.
type scanner interface{ Scan(dest ...any) error }

func scanPost(s scanner) (*Post, error) {
	var p Post
	var created, updated int64
	var published sql.NullInt64
	err := s.Scan(&p.ID, &p.Slug, &p.Title, &p.Subtitle, &p.Status, &created, &updated, &published)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("scan post: %w", err)
	}
	p.CreatedAt = time.Unix(created, 0)
	p.UpdatedAt = time.Unix(updated, 0)
	if published.Valid {
		t := time.Unix(published.Int64, 0)
		p.PublishedAt = &t
	}
	return &p, nil
}

// updatePost writes the post header. The slug is re-derived from the title
// only when the title actually changed, so an already-linked post keeps its URL
// while you are only fixing a subtitle.
func updatePost(db *sql.DB, id int64, title, subtitle, status string) (*Post, error) {
	cur, err := postByID(db, id)
	if err != nil {
		return nil, err
	}
	slug := cur.Slug
	if title != cur.Title {
		slug, err = uniqueSlug(db, slugify(title))
		if err != nil {
			return nil, err
		}
	}
	if _, err := db.Exec(
		`UPDATE post SET slug = ?, title = ?, subtitle = ?, status = ?, updated_at = ? WHERE id = ?`,
		slug, title, subtitle, status, time.Now().Unix(), id); err != nil {
		return nil, fmt.Errorf("update post: %w", err)
	}
	return postByID(db, id)
}

func deletePost(db *sql.DB, id int64) error {
	// Blocks go with it via ON DELETE CASCADE; assets deliberately survive,
	// since they are content-addressed and may be referenced by other posts.
	if _, err := db.Exec(`DELETE FROM post WHERE id = ?`, id); err != nil {
		return fmt.Errorf("delete post: %w", err)
	}
	return nil
}

// touchPost bumps updated_at, so the post list orders by recent activity even
// when only a block changed.
func touchPost(tx *sql.Tx, postID int64) error {
	_, err := tx.Exec(`UPDATE post SET updated_at = ? WHERE id = ?`, time.Now().Unix(), postID)
	if err != nil {
		return fmt.Errorf("touch post %d: %w", postID, err)
	}
	return nil
}

// slugRe matches everything that is not allowed in a slug.
var slugRe = regexp.MustCompile(`[^a-z0-9]+`)

// slugify turns a title into a URL-safe slug. Non-ASCII characters are dropped
// rather than transliterated: the slug is a URL, and the title remains the
// human-readable form.
func slugify(title string) string {
	s := slugRe.ReplaceAllString(strings.ToLower(title), "-")
	s = strings.Trim(s, "-")
	if s == "" {
		s = "untitled"
	}
	if len(s) > 80 {
		s = strings.Trim(s[:80], "-")
	}
	return s
}

// uniqueSlug appends -2, -3, … until the slug is free. slug is UNIQUE in the
// schema, so this is a convenience, not the correctness guarantee.
func uniqueSlug(db *sql.DB, base string) (string, error) {
	slug := base
	for i := 2; ; i++ {
		var n int
		if err := db.QueryRow(`SELECT COUNT(*) FROM post WHERE slug = ?`, slug).Scan(&n); err != nil {
			return "", fmt.Errorf("check slug: %w", err)
		}
		if n == 0 {
			return slug, nil
		}
		slug = fmt.Sprintf("%s-%d", base, i)
	}
}

// ---------------------------------------------------------------------------
// Blocks

// blocksOfPost loads a post's blocks in order, with their assets attached.
func blocksOfPost(db *sql.DB, postID int64) ([]Block, error) {
	rows, err := db.Query(`
		SELECT b.id, b.post_id, b.position, b.kind, b.content, b.asset_id, b.meta,
		       a.id, a.sha256, a.kind, a.mime, a.filename, a.width, a.height, a.created_at
		  FROM block b
		  LEFT JOIN asset a ON a.id = b.asset_id
		 WHERE b.post_id = ?
		 ORDER BY b.position`, postID)
	if err != nil {
		return nil, fmt.Errorf("list blocks: %w", err)
	}
	defer rows.Close()

	var out []Block
	for rows.Next() {
		var b Block
		var metaJSON string
		var aID sql.NullInt64
		var aSHA, aKind, aMIME, aName sql.NullString
		var aW, aH, aCreated sql.NullInt64
		if err := rows.Scan(
			&b.ID, &b.PostID, &b.Position, &b.Kind, &b.Content, &b.AssetID, &metaJSON,
			&aID, &aSHA, &aKind, &aMIME, &aName, &aW, &aH, &aCreated,
		); err != nil {
			return nil, fmt.Errorf("scan block: %w", err)
		}
		// A malformed meta blob must not take down the whole post; an empty
		// meta just means the block renders without its optional extras.
		if err := json.Unmarshal([]byte(metaJSON), &b.Meta); err != nil {
			b.Meta = BlockMeta{}
		}
		if aID.Valid {
			b.Asset = &Asset{
				ID: aID.Int64, SHA256: aSHA.String, Kind: aKind.String,
				MIME: aMIME.String, Filename: aName.String,
				Width: int(aW.Int64), Height: int(aH.Int64),
				CreatedAt: time.Unix(aCreated.Int64, 0),
			}
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

// insertBlock adds a block to a post.
//
// after is the position to insert behind: -1 puts the block first, and any
// value at or beyond the last position appends. This is what "drop a file onto
// a block and get a new block right after it" turns into.
func insertBlock(db *sql.DB, postID int64, after int, kind, content string, assetID *int64, meta BlockMeta) (*Block, error) {
	if !validKinds[kind] {
		return nil, fmt.Errorf("unknown block kind %q", kind)
	}
	metaJSON, err := json.Marshal(meta)
	if err != nil {
		return nil, fmt.Errorf("encode meta: %w", err)
	}

	tx, err := db.Begin()
	if err != nil {
		return nil, fmt.Errorf("begin insert block: %w", err)
	}
	defer tx.Rollback()

	// The foreign key would catch this too, but only as an opaque constraint
	// violation. Checking here reports it as the missing post it is.
	var exists int
	if err := tx.QueryRow(`SELECT COUNT(*) FROM post WHERE id = ?`, postID).Scan(&exists); err != nil {
		return nil, fmt.Errorf("check post %d: %w", postID, err)
	}
	if exists == 0 {
		return nil, ErrNotFound
	}

	var count int
	if err := tx.QueryRow(`SELECT COUNT(*) FROM block WHERE post_id = ?`, postID).Scan(&count); err != nil {
		return nil, fmt.Errorf("count blocks: %w", err)
	}
	pos := after + 1
	if pos < 0 {
		pos = 0
	}
	if pos > count {
		pos = count
	}
	// Open a gap by pushing everything at or after pos up one. The renumber
	// below then normalises the sequence, so this only needs to be
	// order-preserving, not gap-free.
	if _, err := tx.Exec(
		`UPDATE block SET position = position + 1 WHERE post_id = ? AND position >= ?`,
		postID, pos); err != nil {
		return nil, fmt.Errorf("open gap: %w", err)
	}
	res, err := tx.Exec(
		`INSERT INTO block (post_id, position, kind, content, asset_id, meta) VALUES (?, ?, ?, ?, ?, ?)`,
		postID, pos, kind, content, assetID, string(metaJSON))
	if err != nil {
		return nil, fmt.Errorf("insert block: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("block id: %w", err)
	}
	if err := renumber(tx, postID); err != nil {
		return nil, err
	}
	if err := touchPost(tx, postID); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, fmt.Errorf("commit insert block: %w", err)
	}
	return &Block{ID: id, PostID: postID, Position: pos, Kind: kind, Content: content, AssetID: assetID, Meta: meta}, nil
}

// updateBlock rewrites a block's payload. This is the autosave path, so it is
// called often and deliberately touches only one row plus the post timestamp.
func updateBlock(db *sql.DB, id int64, content string, meta BlockMeta) error {
	metaJSON, err := json.Marshal(meta)
	if err != nil {
		return fmt.Errorf("encode meta: %w", err)
	}
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("begin update block: %w", err)
	}
	defer tx.Rollback()

	var postID int64
	if err := tx.QueryRow(`SELECT post_id FROM block WHERE id = ?`, id).Scan(&postID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrNotFound
		}
		return fmt.Errorf("find block %d: %w", id, err)
	}
	if _, err := tx.Exec(
		`UPDATE block SET content = ?, meta = ? WHERE id = ?`,
		content, string(metaJSON), id); err != nil {
		return fmt.Errorf("update block %d: %w", id, err)
	}
	if err := touchPost(tx, postID); err != nil {
		return err
	}
	return tx.Commit()
}

// moveBlock moves a block to an absolute target position within its post,
// clamped to the valid range.
func moveBlock(db *sql.DB, id int64, target int) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("begin move block: %w", err)
	}
	defer tx.Rollback()

	var postID int64
	var cur int
	if err := tx.QueryRow(`SELECT post_id, position FROM block WHERE id = ?`, id).Scan(&postID, &cur); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrNotFound
		}
		return fmt.Errorf("find block %d: %w", id, err)
	}
	var count int
	if err := tx.QueryRow(`SELECT COUNT(*) FROM block WHERE post_id = ?`, postID).Scan(&count); err != nil {
		return fmt.Errorf("count blocks: %w", err)
	}
	if target < 0 {
		target = 0
	}
	if target > count-1 {
		target = count - 1
	}
	if target == cur {
		return tx.Commit()
	}
	// Shift the span between the old and new slot by one, in the direction
	// that keeps every other block's relative order intact, then drop the
	// moved block into the vacated slot.
	if target < cur {
		if _, err := tx.Exec(
			`UPDATE block SET position = position + 1
			  WHERE post_id = ? AND position >= ? AND position < ?`,
			postID, target, cur); err != nil {
			return fmt.Errorf("shift down: %w", err)
		}
	} else {
		if _, err := tx.Exec(
			`UPDATE block SET position = position - 1
			  WHERE post_id = ? AND position > ? AND position <= ?`,
			postID, cur, target); err != nil {
			return fmt.Errorf("shift up: %w", err)
		}
	}
	if _, err := tx.Exec(`UPDATE block SET position = ? WHERE id = ?`, target, id); err != nil {
		return fmt.Errorf("place block: %w", err)
	}
	if err := renumber(tx, postID); err != nil {
		return err
	}
	if err := touchPost(tx, postID); err != nil {
		return err
	}
	return tx.Commit()
}

func deleteBlock(db *sql.DB, id int64) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("begin delete block: %w", err)
	}
	defer tx.Rollback()

	var postID int64
	if err := tx.QueryRow(`SELECT post_id FROM block WHERE id = ?`, id).Scan(&postID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrNotFound
		}
		return fmt.Errorf("find block %d: %w", id, err)
	}
	if _, err := tx.Exec(`DELETE FROM block WHERE id = ?`, id); err != nil {
		return fmt.Errorf("delete block %d: %w", id, err)
	}
	if err := renumber(tx, postID); err != nil {
		return err
	}
	if err := touchPost(tx, postID); err != nil {
		return err
	}
	return tx.Commit()
}

// renumber rewrites a post's block positions to exactly 0..n-1, preserving the
// current order. Called at the end of every operation that can disturb the
// sequence, which is what keeps "position" a dense index rather than an
// ever-growing set of arbitrary integers with gaps.
//
// Ties are broken by id, so two blocks that somehow share a position get a
// deterministic (creation) order rather than whatever the query planner felt
// like returning.
func renumber(tx *sql.Tx, postID int64) error {
	rows, err := tx.Query(`SELECT id FROM block WHERE post_id = ? ORDER BY position, id`, postID)
	if err != nil {
		return fmt.Errorf("renumber query: %w", err)
	}
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			rows.Close()
			return fmt.Errorf("renumber scan: %w", err)
		}
		ids = append(ids, id)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return fmt.Errorf("renumber rows: %w", err)
	}
	for i, id := range ids {
		if _, err := tx.Exec(`UPDATE block SET position = ? WHERE id = ?`, i, id); err != nil {
			return fmt.Errorf("renumber block %d: %w", id, err)
		}
	}
	return nil
}