5 files changed, 267 insertions, 63 deletions

diff --git a/internal/store/blogs.go b/internal/store/blogs.go
index 3117154..579d73d 100644
--- a/internal/store/blogs.go
+++ b/internal/store/blogs.go
@@ -7,6 +7,7 @@ import (
 	"fmt"
 	"time"
 
+	"github.com/gramanas/blogspace/internal/config"
 	"github.com/gramanas/blogspace/internal/db"
 	"github.com/jackc/pgx/v5"
 )
@@ -19,6 +20,8 @@ type Blog struct {
 	Subdomain string
 	DBName    string
 	CreatedAt time.Time
+	// Per-file upload limit set by the superadmin; 0 means the server default.
+	MaxUploadBytes int64
 	// from the blog database
 	Title     string
 	Tagline   string
@@ -27,17 +30,25 @@ type Blog struct {
 	UpdatedAt time.Time
 }
 
-const blogCols = `id, owner_id, subdomain, db_name, created_at`
+const blogCols = `id, owner_id, subdomain, db_name, created_at, COALESCE(max_upload_bytes, 0)`
 
 func scanBlog(row interface{ Scan(...any) error }) (*Blog, error) {
 	var b Blog
-	err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.DBName, &b.CreatedAt)
+	err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.DBName, &b.CreatedAt, &b.MaxUploadBytes)
 	if err != nil {
 		return nil, wrap(err)
 	}
 	return &b, nil
 }
 
+// UploadLimit is the per-file limit that applies to this blog.
+func (b *Blog) UploadLimit(cfg *config.Config) int64 {
+	if b.MaxUploadBytes > 0 {
+		return b.MaxUploadBytes
+	}
+	return cfg.MaxUploadBytes
+}
+
 func (bs *BlogStore) loadSettings(ctx context.Context, b *Blog) error {
 	err := bs.db.QueryRow(ctx, `SELECT title, tagline, language, theme, updated_at FROM settings`).Scan(&b.Title, &b.Tagline, &b.Language, &b.ThemeJSON, &b.UpdatedAt)
 	if errors.Is(err, pgx.ErrNoRows) { // registered, but the database is empty: not a 404
@@ -174,6 +185,12 @@ func (s *Store) DeleteBlog(ctx context.Context, b *Blog) error {
 	return s.cluster.DropBlogDB(ctx, b.DBName)
 }
 
+// SetBlogUploadLimit overrides the upload limit of one blog; 0 restores the server default.
+func (s *Store) SetBlogUploadLimit(ctx context.Context, id int64, bytes int64) error {
+	_, err := s.db.Exec(ctx, `UPDATE blogs SET max_upload_bytes = NULLIF($2, 0) WHERE id=$1`, id, bytes)
+	return err
+}
+
 func (bs *BlogStore) UpdateSettings(ctx context.Context, title, tagline, language string) error {
 	_, err := bs.db.Exec(ctx, `UPDATE settings SET title=$1, tagline=$2, language=$3, updated_at=now()`, title, tagline, language)
 	return err
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
+}
diff --git a/internal/store/files_test.go b/internal/store/files_test.go
new file mode 100644
index 0000000..dfe7d55
--- /dev/null
+++ b/internal/store/files_test.go
@@ -0,0 +1,69 @@
+package store
+
+import (
+	"bytes"
+	"io"
+	"net/http"
+	"net/http/httptest"
+	"testing"
+	"time"
+)
+
+// A reader over an in-memory slice, counting the fetches a query would cost.
+func testReader(data []byte, chunk int64) (*chunkReader, *int) {
+	calls := 0
+	return newChunkReader(int64(len(data)), chunk, func(off, n int64) ([]byte, error) {
+		calls++
+		return data[off : off+n], nil
+	}), &calls
+}
+
+func TestChunkReader(t *testing.T) {
+	data := make([]byte, 3<<20+7)
+	for i := range data {
+		data[i] = byte(i * 31)
+	}
+	r, calls := testReader(data, 1000)
+	got, err := io.ReadAll(r)
+	if err != nil || !bytes.Equal(got, data) {
+		t.Fatalf("ReadAll: err %v, %d bytes", err, len(got))
+	}
+	if want := (len(data) + 999) / 1000; *calls != want {
+		t.Errorf("%d fetches, want %d", *calls, want)
+	}
+	if n, _ := r.Seek(0, io.SeekEnd); n != int64(len(data)) {
+		t.Errorf("SeekEnd = %d", n)
+	}
+	if _, err := r.Seek(-1, io.SeekStart); err == nil {
+		t.Error("negative seek should fail")
+	}
+	r.Seek(1500, io.SeekStart)
+	buf := make([]byte, 1200)
+	if _, err := io.ReadFull(r, buf); err != nil || !bytes.Equal(buf, data[1500:2700]) {
+		t.Errorf("read after seek: %v", err)
+	}
+	if _, err := r.Read(buf); r.off != 2700 && err != nil {
+		t.Errorf("continuing read: %v", err)
+	}
+}
+
+func TestChunkReaderServeContent(t *testing.T) {
+	data := bytes.Repeat([]byte("0123456789"), 500)
+	r, calls := testReader(data, 512)
+	w := httptest.NewRecorder()
+	req := httptest.NewRequest("GET", "/media/x", nil)
+	req.Header.Set("Range", "bytes=1500-2499")
+	http.ServeContent(w, req, "x.bin", time.Now(), r)
+	if w.Code != http.StatusPartialContent || !bytes.Equal(w.Body.Bytes(), data[1500:2500]) || w.Header().Get("Content-Range") != "bytes 1500-2499/5000" {
+		t.Errorf("range: code %d, %d bytes, %s", w.Code, w.Body.Len(), w.Header().Get("Content-Range"))
+	}
+	if *calls != 2 {
+		t.Errorf("a 1000-byte range cost %d fetches, want 2", *calls)
+	}
+	r, calls = testReader(data, 512)
+	w = httptest.NewRecorder()
+	http.ServeContent(w, httptest.NewRequest("HEAD", "/media/x", nil), "x.bin", time.Now(), r)
+	if w.Code != http.StatusOK || w.Header().Get("Content-Length") != "5000" || *calls != 0 {
+		t.Errorf("HEAD: code %d, length %s, %d fetches", w.Code, w.Header().Get("Content-Length"), *calls)
+	}
+}
diff --git a/internal/store/images.go b/internal/store/images.go
deleted file mode 100644
index b222468..0000000
--- a/internal/store/images.go
+++ /dev/null
@@ -1,57 +0,0 @@
-package store
-
-import (
-	"context"
-	"time"
-
-	"github.com/google/uuid"
-)
-
-type Image struct {
-	ID          uuid.UUID
-	Filename    string
-	ContentType string
-	Size        int
-	Data        []byte // only populated by ImageData
-	CreatedAt   time.Time
-}
-
-func (bs *BlogStore) CreateImage(ctx context.Context, filename, contentType string, data []byte) (*Image, error) {
-	img := &Image{ID: uuid.New(), Filename: filename, ContentType: contentType, Size: len(data)}
-	err := bs.db.QueryRow(ctx, `INSERT INTO images (id, filename, content_type, size, data) VALUES ($1,$2,$3,$4,$5) RETURNING created_at`,
-		img.ID, filename, contentType, len(data), data).Scan(&img.CreatedAt)
-	return img, err
-}
-
-func (bs *BlogStore) ListImages(ctx context.Context) ([]Image, error) {
-	rows, err := bs.db.Query(ctx, `SELECT id, filename, content_type, size, created_at FROM images ORDER BY created_at DESC`)
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-	var out []Image
-	for rows.Next() {
-		var i Image
-		if err := rows.Scan(&i.ID, &i.Filename, &i.ContentType, &i.Size, &i.CreatedAt); err != nil {
-			return nil, err
-		}
-		out = append(out, i)
-	}
-	return out, rows.Err()
-}
-
-// ImageData loads an image including its bytes.
-func (bs *BlogStore) ImageData(ctx context.Context, id uuid.UUID) (*Image, error) {
-	var i Image
-	err := bs.db.QueryRow(ctx, `SELECT id, filename, content_type, size, data, created_at FROM images WHERE id=$1`, id).
-		Scan(&i.ID, &i.Filename, &i.ContentType, &i.Size, &i.Data, &i.CreatedAt)
-	if err != nil {
-		return nil, wrap(err)
-	}
-	return &i, nil
-}
-
-func (bs *BlogStore) DeleteImage(ctx context.Context, id uuid.UUID) error {
-	_, err := bs.db.Exec(ctx, `DELETE FROM images WHERE id=$1`, id)
-	return err
-}
diff --git a/internal/store/users.go b/internal/store/users.go
index 0c47c24..3edc7a9 100644
--- a/internal/store/users.go
+++ b/internal/store/users.go
@@ -90,13 +90,14 @@ func (s *Store) DeleteUser(ctx context.Context, id int64) error {
 // UserWithBlog is a row for the admin overview.
 type UserWithBlog struct {
 	User
-	BlogID    *int64
-	Subdomain *string
+	BlogID         *int64
+	Subdomain      *string
+	MaxUploadBytes int64 // 0 = server default
 }
 
 func (s *Store) ListUsers(ctx context.Context) ([]UserWithBlog, error) {
 	rows, err := s.db.Query(ctx, `SELECT u.id, u.username, u.password_hash, u.role, u.disabled, u.token_version, u.created_at,
-		b.id, b.subdomain
+		b.id, b.subdomain, COALESCE(b.max_upload_bytes, 0)
 		FROM users u LEFT JOIN blogs b ON b.owner_id = u.id
 		ORDER BY u.role, u.username`)
 	if err != nil {
@@ -107,7 +108,7 @@ func (s *Store) ListUsers(ctx context.Context) ([]UserWithBlog, error) {
 	for rows.Next() {
 		var r UserWithBlog
 		if err := rows.Scan(&r.ID, &r.Username, &r.PasswordHash, &r.Role, &r.Disabled, &r.TokenVersion, &r.CreatedAt,
-			&r.BlogID, &r.Subdomain); err != nil {
+			&r.BlogID, &r.Subdomain, &r.MaxUploadBytes); err != nil {
 			return nil, err
 		}
 		out = append(out, r)