1 files changed, 174 insertions, 0 deletions
diff --git a/internal/store/files.go b/internal/store/files.go
new file mode 100644
index 0000000..287b4d7
--- /dev/null
+++ b/internal/store/files.go
@@ -0,0 +1,174 @@
+package store
+
+import (
+	"context"
+	"errors"
+	"io"
+	"path"
+	"strings"
+	"time"
+
+	"github.com/google/uuid"
+)
+
+// File is an upload in the blog's library: an image, a document, an archive…
+// The bytes are never loaded with it; FileReader streams them.
+type File struct {
+	ID          uuid.UUID
+	Filename    string
+	ContentType string
+	Kind        string // image, document, audio, video, archive, other (CHECK in the schema)
+	Size        int64
+	CreatedAt   time.Time
+}
+
+// Badge is the short label the dashboard shows instead of a thumbnail: the
+// extension in capitals, or FILE when there is none.
+func (f File) Badge() string {
+	ext := strings.ToUpper(strings.TrimPrefix(path.Ext(f.Filename), "."))
+	if ext == "" || len([]rune(ext)) > 5 {
+		return "FILE"
+	}
+	return ext
+}
+
+const fileCols = `id, filename, content_type, kind, size, created_at`
+
+func scanFile(row interface{ Scan(...any) error }) (*File, error) {
+	var f File
+	if err := row.Scan(&f.ID, &f.Filename, &f.ContentType, &f.Kind, &f.Size, &f.CreatedAt); err != nil {
+		return nil, wrap(err)
+	}
+	return &f, nil
+}
+
+func (bs *BlogStore) CreateFile(ctx context.Context, filename, contentType, kind string, data []byte) (*File, error) {
+	f := &File{ID: uuid.New(), Filename: filename, ContentType: contentType, Kind: kind, Size: int64(len(data))}
+	err := bs.db.QueryRow(ctx, `INSERT INTO files (id, filename, content_type, kind, size, data) VALUES ($1,$2,$3,$4,$5,$6) RETURNING created_at`,
+		f.ID, filename, contentType, kind, f.Size, data).Scan(&f.CreatedAt)
+	return f, err
+}
+
+// likeEscape makes user text safe inside an ILIKE pattern.
+var likeEscape = strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`)
+
+// ListFiles is one page of the library, newest first, with the total that
+// matches the same filter. kind "" is every kind, q "" no filename filter and
+// limit 0 no limit (the design page's image picker).
+func (bs *BlogStore) ListFiles(ctx context.Context, kind, q string, limit, offset int) ([]File, int, error) {
+	const where = ` WHERE ($1 = '' OR kind = $1) AND ($2 = '' OR filename ILIKE '%' || $2 || '%')`
+	q = likeEscape.Replace(q)
+	var total int
+	if err := bs.db.QueryRow(ctx, `SELECT count(*) FROM files`+where, kind, q).Scan(&total); err != nil {
+		return nil, 0, err
+	}
+	rows, err := bs.db.Query(ctx, `SELECT `+fileCols+` FROM files`+where+` ORDER BY created_at DESC, id DESC LIMIT NULLIF($3, 0) OFFSET $4`,
+		kind, q, limit, offset)
+	if err != nil {
+		return nil, 0, err
+	}
+	defer rows.Close()
+	var out []File
+	for rows.Next() {
+		f, err := scanFile(rows)
+		if err != nil {
+			return nil, 0, err
+		}
+		out = append(out, *f)
+	}
+	return out, total, rows.Err()
+}
+
+// FileMeta loads a file without its bytes.
+func (bs *BlogStore) FileMeta(ctx context.Context, id uuid.UUID) (*File, error) {
+	return scanFile(bs.db.QueryRow(ctx, `SELECT `+fileCols+` FROM files WHERE id=$1`, id))
+}
+
+// FileReader streams a file's bytes in slices, so serving it never holds the
+// whole blob in memory (the column is STORAGE EXTERNAL: substring is cheap).
+func (bs *BlogStore) FileReader(ctx context.Context, f *File) io.ReadSeeker {
+	return newChunkReader(f.Size, fileChunk, func(off, n int64) ([]byte, error) {
+		var b []byte
+		// substring is 1-based and its arguments are int4: the upload limit is capped so offsets fit.
+		err := bs.db.QueryRow(ctx, `SELECT substring(data FROM $2 FOR $3) FROM files WHERE id=$1`, f.ID, int32(off+1), int32(n)).Scan(&b)
+		return b, wrap(err)
+	})
+}
+
+func (bs *BlogStore) RenameFile(ctx context.Context, id uuid.UUID, filename string) error {
+	tag, err := bs.db.Exec(ctx, `UPDATE files SET filename=$2 WHERE id=$1`, id, filename)
+	if err == nil && tag.RowsAffected() == 0 {
+		return ErrNotFound
+	}
+	return err
+}
+
+func (bs *BlogStore) DeleteFile(ctx context.Context, id uuid.UUID) error {
+	_, err := bs.db.Exec(ctx, `DELETE FROM files WHERE id=$1`, id)
+	return err
+}
+
+// FileUsage is what the library holds: how many files and their bytes.
+func (bs *BlogStore) FileUsage(ctx context.Context) (count int, bytes int64, err error) {
+	err = bs.db.QueryRow(ctx, `SELECT count(*), COALESCE(sum(size), 0) FROM files`).Scan(&count, &bytes)
+	return count, bytes, err
+}
+
+// fileChunk is how much of a file one query fetches: 20 round trips for a
+// 10 MB download, and a bounded buffer per request whatever the file size.
+const fileChunk = 512 << 10
+
+// chunkReader is an io.ReadSeeker over bytes fetched in slices, which is what
+// http.ServeContent needs to stream a file and honour Range requests. fetch
+// returns data[off:off+n]. Seek never fetches: ServeContent seeks to the end
+// and back to learn the size before reading anything.
+type chunkReader struct {
+	size, off int64
+	chunk     int64
+	fetch     func(off, n int64) ([]byte, error)
+	buf       []byte // data[bufOff : bufOff+len(buf)]
+	bufOff    int64
+}
+
+func newChunkReader(size, chunk int64, fetch func(off, n int64) ([]byte, error)) *chunkReader {
+	return &chunkReader{size: size, chunk: chunk, fetch: fetch}
+}
+
+func (r *chunkReader) Read(p []byte) (int, error) {
+	if r.off >= r.size {
+		return 0, io.EOF
+	}
+	if r.buf == nil || r.off < r.bufOff || r.off >= r.bufOff+int64(len(r.buf)) {
+		n := min(r.chunk, r.size-r.off)
+		b, err := r.fetch(r.off, n)
+		if err != nil {
+			return 0, err
+		}
+		if int64(len(b)) < n { // shorter than the row said: the file changed underneath us
+			return 0, io.ErrUnexpectedEOF
+		}
+		r.buf, r.bufOff = b, r.off
+	}
+	n := copy(p, r.buf[r.off-r.bufOff:])
+	r.off += int64(n)
+	return n, nil
+}
+
+func (r *chunkReader) Seek(offset int64, whence int) (int64, error) {
+	var abs int64
+	switch whence {
+	case io.SeekStart:
+		abs = offset
+	case io.SeekCurrent:
+		abs = r.off + offset
+	case io.SeekEnd:
+		abs = r.size + offset
+	default:
+		return 0, errors.New("chunkReader: invalid whence")
+	}
+	if abs < 0 {
+		return 0, errors.New("chunkReader: negative position")
+	}
+	r.off = abs
+	return abs, nil
+}