1package prose
  2
  3import (
  4	"fmt"
  5	"sync"
  6	"time"
  7
  8	"github.com/hashicorp/golang-lru/v2/expirable"
  9	"github.com/picosh/pico/pkg/db"
 10	"github.com/picosh/pico/pkg/db/postgres"
 11)
 12
 13// DB is the storage prose uses.
 14type DB interface {
 15	db.AuthDB
 16	db.PostDB
 17	db.TagDB
 18	FindUserPostsByTag(pager *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error)
 19	FindPopularPosts() ([]*db.Post, error)
 20}
 21
 22// PsqlDB is prose's postgres storage: the queries apps share, and the ones
 23// only prose makes.
 24type PsqlDB struct {
 25	*postgres.PsqlDB
 26
 27	popularPostsCache *expirable.LRU[string, []*db.Post]
 28	popularPostsMu    sync.Mutex
 29}
 30
 31var _ DB = (*PsqlDB)(nil)
 32
 33func NewDB(base *postgres.PsqlDB) *PsqlDB {
 34	return &PsqlDB{
 35		PsqlDB:            base,
 36		popularPostsCache: expirable.NewLRU[string, []*db.Post](16, nil, 15*time.Minute),
 37	}
 38}
 39
 40func (me *PsqlDB) ClearPopularPostsCache() {
 41	if me.popularPostsCache != nil {
 42		me.popularPostsCache.Purge()
 43	}
 44}
 45
 46func (me *PsqlDB) FindPopularPosts() ([]*db.Post, error) {
 47	if me.popularPostsCache != nil {
 48		if posts, ok := me.popularPostsCache.Get("popular_posts"); ok {
 49			return posts, nil
 50		}
 51	}
 52
 53	me.popularPostsMu.Lock()
 54	defer me.popularPostsMu.Unlock()
 55
 56	if me.popularPostsCache != nil {
 57		if posts, ok := me.popularPostsCache.Get("popular_posts"); ok {
 58			return posts, nil
 59		}
 60	}
 61
 62	query := `
 63	WITH candidate_posts AS (
 64	    SELECT
 65	        posts.id,
 66	        posts.user_id,
 67	        posts.filename,
 68	        posts.slug,
 69	        posts.title,
 70	        posts.text,
 71	        posts.description,
 72	        posts.publish_at,
 73	        app_users.name AS username,
 74	        posts.updated_at,
 75	        posts.mime_type
 76	    FROM posts
 77	    LEFT JOIN app_users ON app_users.id = posts.user_id
 78	    WHERE
 79	        hidden = FALSE
 80	        AND publish_at::date <= CURRENT_DATE
 81	        AND publish_at >= NOW() - INTERVAL '30 days'
 82	        AND cur_space = 'prose'
 83	        AND EXISTS (
 84	            SELECT 1 FROM feature_flags
 85	            WHERE feature_flags.user_id = posts.user_id
 86	              AND (name = 'plus' OR name = 'prose')
 87	              AND expires_at > NOW()
 88	        )
 89	),
 90	post_visitors AS (
 91	    SELECT post_id, COUNT(DISTINCT ip_address) AS unique_visitors
 92	    FROM analytics_visits
 93	    WHERE post_id IN (SELECT id FROM candidate_posts)
 94	      AND status = 200
 95	      AND created_at >= NOW() - INTERVAL '30 days'
 96	    GROUP BY post_id
 97	),
 98	scored_posts AS (
 99	    SELECT
100	        cp.*,
101	        COALESCE(pv.unique_visitors, 0) AS score,
102	        (
103	            LOG(GREATEST(COALESCE(pv.unique_visitors, 0), 1)) +
104	            (EXTRACT(EPOCH FROM cp.publish_at) - 1700000000) / 345600.0
105	        ) AS rank_score,
106	        ROW_NUMBER() OVER (
107	            PARTITION BY cp.user_id
108	            ORDER BY (
109	                LOG(GREATEST(COALESCE(pv.unique_visitors, 0), 1)) +
110	                (EXTRACT(EPOCH FROM cp.publish_at) - 1700000000) / 345600.0
111	            ) DESC, cp.publish_at DESC
112	        ) AS user_rank
113	    FROM candidate_posts cp
114	    LEFT JOIN post_visitors pv ON pv.post_id = cp.id
115	)
116	SELECT
117	    id,
118	    user_id,
119	    filename,
120	    slug,
121	    title,
122	    text,
123	    description,
124	    publish_at,
125	    username,
126	    updated_at,
127	    mime_type,
128	    score
129	FROM scored_posts
130	WHERE user_rank <= 3
131	ORDER BY rank_score DESC, publish_at DESC
132	LIMIT 30`
133	rs, err := me.Db.Queryx(query)
134	if err != nil {
135		return nil, err
136	}
137	defer func() { _ = rs.Close() }()
138
139	var posts []*db.Post
140	for rs.Next() {
141		post := &db.Post{}
142		err := rs.Scan(
143			&post.ID,
144			&post.UserID,
145			&post.Filename,
146			&post.Slug,
147			&post.Title,
148			&post.Text,
149			&post.Description,
150			&post.PublishAt,
151			&post.Username,
152			&post.UpdatedAt,
153			&post.MimeType,
154			&post.Score,
155		)
156		if err != nil {
157			return nil, err
158		}
159
160		posts = append(posts, post)
161	}
162	if rs.Err() != nil {
163		return nil, rs.Err()
164	}
165
166	if me.popularPostsCache != nil {
167		me.popularPostsCache.Add("popular_posts", posts)
168	}
169
170	return posts, nil
171}
172
173func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
174	var posts []*db.Post
175	query := fmt.Sprintf(`
176	SELECT %s
177	FROM posts
178	LEFT JOIN app_users ON app_users.id = posts.user_id
179	LEFT JOIN post_tags ON post_tags.post_id = posts.id
180	WHERE
181		hidden = FALSE AND
182		user_id = $1 AND
183		(post_tags.name = $2 OR hidden = true) AND
184		publish_at::date <= CURRENT_DATE AND
185		cur_space = $3
186	ORDER BY publish_at DESC
187	LIMIT $4 OFFSET $5`, postgres.SelectPost)
188	err := me.Db.Select(
189		&posts,
190		query,
191		userID,
192		tag,
193		space,
194		page.Num,
195		page.Num*page.Page,
196	)
197	if err != nil {
198		return nil, err
199	}
200
201	return &db.Paginate[*db.Post]{Data: posts}, nil
202}