80 lines
2.3 KiB
Go
80 lines
2.3 KiB
Go
package trending
|
|
|
|
import (
|
|
"database/sql"
|
|
"encoding/json"
|
|
)
|
|
|
|
// GetTrendingScoreKind20 returns the top 20 trending posts of kind 20 from the last 24 hours
|
|
func GetTrendingScoreKind20(db *sql.DB) ([]Post, error) {
|
|
if cached, ok := trendingCache.Get("trending_kind_20"); ok {
|
|
return cached.([]Post), nil
|
|
}
|
|
|
|
query := `
|
|
WITH engagement AS (
|
|
SELECT
|
|
tags_expanded.value->1 #>> '{}' AS original_event_id,
|
|
e.kind::text,
|
|
extract(epoch from now()) - e.created_at as seconds_ago,
|
|
COUNT(*) as count
|
|
FROM event e
|
|
CROSS JOIN LATERAL jsonb_array_elements(tags) as tags_expanded(value)
|
|
WHERE e.kind::text IN ('1', '6', '7','9735') -- replies, reposts, reactions, zaps
|
|
AND e.created_at >= extract(epoch from now() - interval '24 hours')::bigint
|
|
AND tags_expanded.value->0 #>> '{}' = 'e'
|
|
GROUP BY tags_expanded.value->1 #>> '{}', e.kind::text, e.created_at
|
|
),
|
|
scores AS (
|
|
SELECT
|
|
original_event_id,
|
|
SUM(
|
|
CASE
|
|
WHEN kind = '7' THEN count * 1.0 -- reactions weight
|
|
WHEN kind = '6' THEN count * 2.0 -- reposts weight
|
|
WHEN kind = '1' THEN count * 1.5 -- replies weight
|
|
WHEN kind = '9735' THEN count * 1.3 -- zaps weight
|
|
END / power((seconds_ago + 7200) / 3600, 1.8) -- time decay
|
|
) as trending_score
|
|
FROM engagement
|
|
GROUP BY original_event_id
|
|
)
|
|
SELECT
|
|
e.id,
|
|
e.pubkey,
|
|
to_timestamp(e.created_at) as created_at,
|
|
e.kind,
|
|
e.content,
|
|
e.tags,
|
|
COALESCE(s.trending_score, 0.0) as reaction_count -- Cast to float
|
|
FROM event e
|
|
LEFT JOIN scores s ON e.id = s.original_event_id
|
|
WHERE e.kind::text = '20'
|
|
AND e.created_at >= extract(epoch from now() - interval '24 hours')::bigint
|
|
ORDER BY reaction_count DESC, e.created_at DESC
|
|
LIMIT 20
|
|
`
|
|
|
|
rows, err := db.Query(query)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var trendingPosts []Post
|
|
for rows.Next() {
|
|
var post Post
|
|
var tagsJSON []byte
|
|
if err := rows.Scan(&post.ID, &post.PubKey, &post.CreatedAt, &post.Kind, &post.Content, &tagsJSON, &post.ReactionCount); err != nil {
|
|
return nil, err
|
|
}
|
|
if err := json.Unmarshal(tagsJSON, &post.Tags); err != nil {
|
|
return nil, err
|
|
}
|
|
trendingPosts = append(trendingPosts, post)
|
|
}
|
|
|
|
trendingCache.Set("trending_kind_20", trendingPosts, cacheDuration)
|
|
return trendingPosts, nil
|
|
}
|