[feature] Cleanup unattached local media (#680)

* add localUnattached db function

* add parseOlderThan util function

* add pruneunusedlocalattachments to media manager

* add unusedlocal pruning to schedule + admin call

* set number of days to keep as a const

* fix test

3 files changed, 40 insertions, 0 deletions

diff --git a/internal/db/bundb/media.go b/internal/db/bundb/media.go
index fc3280ddf..71433b901 100644
--- a/internal/db/bundb/media.go
+++ b/internal/db/bundb/media.go
@@ -98,3 +98,29 @@ func (m *mediaDB) GetAvatarsAndHeaders(ctx context.Context, maxID string, limit
 
 	return attachments, nil
 }
+
+func (m *mediaDB) GetLocalUnattachedOlderThan(ctx context.Context, olderThan time.Time, maxID string, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
+	attachments := []*gtsmodel.MediaAttachment{}
+
+	q := m.newMediaQ(&attachments).
+		Where("media_attachment.cached = true").
+		Where("media_attachment.avatar = false").
+		Where("media_attachment.header = false").
+		Where("media_attachment.created_at < ?", olderThan).
+		Where("media_attachment.remote_url IS NULL").
+		Where("media_attachment.status_id IS NULL")
+
+	if maxID != "" {
+		q = q.Where("media_attachment.id < ?", maxID)
+	}
+
+	if limit != 0 {
+		q = q.Limit(limit)
+	}
+
+	if err := q.Scan(ctx); err != nil {
+		return nil, m.conn.ProcessError(err)
+	}
+
+	return attachments, nil
+}
diff --git a/internal/db/bundb/media_test.go b/internal/db/bundb/media_test.go
index f1809b3fb..d6a4981f8 100644
--- a/internal/db/bundb/media_test.go
+++ b/internal/db/bundb/media_test.go
@@ -24,6 +24,7 @@ import (
 	"time"
 
 	"github.com/stretchr/testify/suite"
+	"github.com/superseriousbusiness/gotosocial/testrig"
 )
 
 type MediaTestSuite struct {
@@ -51,6 +52,14 @@ func (suite *MediaTestSuite) TestGetAvisAndHeaders() {
 	suite.Len(attachments, 2)
 }
 
+func (suite *MediaTestSuite) TestGetLocalUnattachedOlderThan() {
+	ctx := context.Background()
+
+	attachments, err := suite.db.GetLocalUnattachedOlderThan(ctx, testrig.TimeMustParse("2090-06-04T13:12:00Z"), "", 10)
+	suite.NoError(err)
+	suite.Len(attachments, 1)
+}
+
 func TestMediaTestSuite(t *testing.T) {
 	suite.Run(t, new(MediaTestSuite))
 }
diff --git a/internal/db/media.go b/internal/db/media.go
index 636fc61f2..2f9ed79dc 100644
--- a/internal/db/media.go
+++ b/internal/db/media.go
@@ -38,4 +38,9 @@ type Media interface {
 	// GetAvatarsAndHeaders fetches limit n avatars and headers with an id < maxID. These headers
 	// and avis may be in use or not; the caller should check this if it's important.
 	GetAvatarsAndHeaders(ctx context.Context, maxID string, limit int) ([]*gtsmodel.MediaAttachment, Error)
+	// GetLocalUnattachedOlderThan fetches limit n local media attachments, older than the given time, which
+	// aren't header or avatars, and aren't attached to a status. In other words, attachments which were uploaded
+	// but never used for whatever reason, or attachments that were attached to a status which was subsequently
+	// deleted.
+	GetLocalUnattachedOlderThan(ctx context.Context, olderThan time.Time, maxID string, limit int) ([]*gtsmodel.MediaAttachment, Error)
 }