mercury/internal/database/sql/timeline.go

package sql

import (
	"context"

	"git.sleepycat.moe/sam/mercury/internal/database"
	"github.com/keegancsmith/sqlf"
	"github.com/oklog/ulid/v2"
)

// TimelineStore is the interface to post timelines in the database.
type TimelineStore struct {
	q Querier
}

// NewTimelineStore creates a new TimelineStore instance.
func NewTimelineStore(q Querier) *TimelineStore {
	return &TimelineStore{q: q}
}

type TimelinePost struct {
	Post database.Post `db:"p"`
	Blog database.Blog `db:"b"`
	Account database.Account `db:"a"`
}

func (s *TimelineStore) Home(ctx context.Context, accountID ulid.ULID, limit int, before, after *ulid.ULID) ([]TimelinePost, error) {
	q := sqlf.Sprintf(`SELECT p.id as "p.id", p.blog_id as "p.blog_id", p.content as "p.content", p.source as "p.source", p.visibility as "p.visibility",
	b.id as "b.id", b.name as "b.name", b.domain as "b.domain", b.bio as "b.bio", b.account_id as "b.account_id",
	a.id as "a.id", a.username as "a.username", a.domain as "a.domain"
	FROM posts p JOIN blogs b ON b.id = p.blog_id JOIN accounts a on a.id = b.account_id`)

	q = sqlf.Sprintf("%v WHERE (blog_id IN (%s) OR blog_id IN (%s))", q,
		sqlf.Sprintf("SELECT id FROM blogs WHERE account_id = %s", accountID),
		sqlf.Sprintf("SELECT blog_id FROM account_follows WHERE account_id = %s", accountID))

	if before != nil {
		q = sqlf.Sprintf("%v AND p.id < %s", q, *before)
	}
	if after != nil {
		q = sqlf.Sprintf("%v AND p.id > %s", q, *after)
	}

	q = sqlf.Sprintf("%v AND (visibility != %s OR (b.account_id = %s OR %s IN (%s)))", q, database.DirectVisibility, accountID, accountID,
		sqlf.Sprintf("SELECT account_id FROM blogs WHERE id IN (SELECT blog_id FROM post_recipients WHERE post_id = p.id)"))

	if limit <= 0 || limit > 100 {
		limit = 100
	}
	q = sqlf.Sprintf("%v ORDER BY p.id DESC LIMIT %d", q, limit)

	return Select[TimelinePost](ctx, s.q, q)
}