Skip to content

armgit / fider

Public Imported from GitHub
0

Open platform to collect and prioritize feedback

19.3 KB
Raw Blame
package postgres

import (
	"context"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/pemistahl/lingua-go"

	"github.com/getfider/fider/app/models/entity"
	"github.com/getfider/fider/app/models/enum"
	"github.com/getfider/fider/app/models/query"
	"github.com/gosimple/slug"
	"github.com/lib/pq"

	"github.com/getfider/fider/app/pkg/bus"
	"github.com/getfider/fider/app/pkg/env"

	"github.com/getfider/fider/app/models/cmd"
	"github.com/getfider/fider/app/pkg/dbx"
	"github.com/getfider/fider/app/pkg/errors"
	"github.com/getfider/fider/app/services/sqlstore/dbEntities"
)

var (
	sqlSelectPostsWhere = `	WITH
													agg_tags AS (
														SELECT
																post_id,
																ARRAY_REMOVE(ARRAY_AGG(tags.slug), NULL) as tags
														FROM post_tags
														INNER JOIN tags
														ON tags.ID = post_tags.TAG_ID
														AND tags.tenant_id = post_tags.tenant_id
														WHERE post_tags.tenant_id = $1
														%s
														GROUP BY post_id
													),
													agg_comments AS (
															SELECT
																	post_id,
																	COUNT(CASE WHEN comments.created_at > CURRENT_DATE - INTERVAL '30 days' AND comments.is_approved = true THEN 1 END) as recent,
																	COUNT(CASE WHEN comments.is_approved = true THEN 1 END) as all
															FROM comments
															INNER JOIN posts
															ON posts.id = comments.post_id
															AND posts.tenant_id = comments.tenant_id
															WHERE posts.tenant_id = $1
															AND comments.deleted_at IS NULL
															GROUP BY post_id
													),
													agg_votes AS (
															SELECT
															post_id,
																	COUNT(CASE WHEN post_votes.created_at > CURRENT_DATE - INTERVAL '30 days'  THEN 1 END) as recent,
																	COUNT(*) as all
															FROM post_votes
															INNER JOIN posts
															ON posts.id = post_votes.post_id
															AND posts.tenant_id = post_votes.tenant_id
															WHERE posts.tenant_id = $1
															GROUP BY post_id
													)
													SELECT p.id,
																p.number,
																p.title,
																p.slug,
																p.description,
																p.created_at,
																p.search,
																COALESCE(agg_s.all, 0) as votes_count,
																COALESCE(agg_c.all, 0) as comments_count,
																COALESCE(agg_s.recent, 0) AS recent_votes_count,
																COALESCE(agg_c.recent, 0) AS recent_comments_count,
																p.status,
																u.id AS user_id,
																u.name AS user_name,
																u.email AS user_email,
																u.role AS user_role,
																u.status AS user_status,
																u.avatar_type AS user_avatar_type,
																u.avatar_bkey AS user_avatar_bkey,
																p.response,
																p.response_date,
																r.id AS response_user_id,
																r.name AS response_user_name,
																r.email AS response_user_email,
																r.role AS response_user_role,
																r.status AS response_user_status,
																r.avatar_type AS response_user_avatar_type,
																r.avatar_bkey AS response_user_avatar_bkey,
																d.number AS original_number,
																d.title AS original_title,
																d.slug AS original_slug,
																d.status AS original_status,
																COALESCE(agg_t.tags, ARRAY[]::text[]) AS tags,
																COALESCE(%s, false) AS has_voted,
																p.is_approved
													FROM posts p
													INNER JOIN users u
													ON u.id = p.user_id
													AND u.tenant_id = $1
													LEFT JOIN users r
													ON r.id = p.response_user_id
													AND r.tenant_id = $1
													LEFT JOIN posts d
													ON d.id = p.original_id
													AND d.tenant_id = $1
													LEFT JOIN agg_comments agg_c
													ON agg_c.post_id = p.id
													LEFT JOIN agg_votes agg_s
													ON agg_s.post_id = p.id
													LEFT JOIN agg_tags agg_t
													ON agg_t.post_id = p.id
													WHERE p.status != ` + strconv.Itoa(int(enum.PostDeleted)) + ` AND %s`
)

func postIsReferenced(ctx context.Context, q *query.PostIsReferenced) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		q.Result = false

		exists, err := trx.Exists(`
			SELECT 1 FROM posts p
			INNER JOIN posts o
			ON o.tenant_id = p.tenant_id
			AND o.id = p.original_id
			WHERE p.tenant_id = $1
			AND o.id = $2`, tenant.ID, q.PostID)
		if err != nil {
			return errors.Wrap(err, "failed to check if post is referenced")
		}

		q.Result = exists
		return nil
	})
}

func setPostResponse(ctx context.Context, c *cmd.SetPostResponse) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		if c.Status == enum.PostDuplicate {
			return errors.New("Use MarkAsDuplicate to change an post status to Duplicate")
		}

		respondedAt := time.Now()
		if c.Post.Status == c.Status && c.Post.Response != nil {
			respondedAt = c.Post.Response.RespondedAt
		}

		_, err := trx.Execute(`
		UPDATE posts
		SET response = $3, original_id = NULL, response_date = $4, response_user_id = $5, status = $6
		WHERE id = $1 and tenant_id = $2
		`, c.Post.ID, tenant.ID, c.Text, respondedAt, user.ID, c.Status)
		if err != nil {
			return errors.Wrap(err, "failed to update post's response")
		}

		if c.Status == enum.PostDeleted {
			_, err = trx.Execute("DELETE FROM notifications WHERE tenant_id = $1 AND post_id = $2", tenant.ID, c.Post.ID)
			if err != nil {
				return errors.Wrap(err, "failed to delete notifications of deleted post")
			}
		}

		c.Post.Status = c.Status
		c.Post.Response = &entity.PostResponse{
			Text:        c.Text,
			RespondedAt: respondedAt,
			User:        user,
		}
		return nil
	})
}

func markPostAsDuplicate(ctx context.Context, c *cmd.MarkPostAsDuplicate) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		respondedAt := time.Now()
		if c.Post.Status == enum.PostDuplicate && c.Post.Response != nil {
			respondedAt = c.Post.Response.RespondedAt
		}

		var users []*dbEntities.User
		err := trx.Select(&users, "SELECT user_id AS id FROM post_votes WHERE post_id = $1 AND tenant_id = $2", c.Post.ID, tenant.ID)
		if err != nil {
			return errors.Wrap(err, "failed to get votes of post with id '%d'", c.Post.ID)
		}

		for _, u := range users {
			err := bus.Dispatch(ctx, &cmd.AddVote{Post: c.Original, User: u.ToModel(ctx)})
			if err != nil {
				return err
			}
		}

		_, err = trx.Execute(`
		UPDATE posts
		SET response = '', original_id = $3, response_date = $4, response_user_id = $5, status = $6
		WHERE id = $1 and tenant_id = $2
		`, c.Post.ID, tenant.ID, c.Original.ID, respondedAt, user.ID, enum.PostDuplicate)
		if err != nil {
			return errors.Wrap(err, "failed to update post's response")
		}

		c.Post.Status = enum.PostDuplicate
		c.Post.Response = &entity.PostResponse{
			RespondedAt: respondedAt,
			User:        user,
			Original: &entity.OriginalPost{
				Number: c.Original.Number,
				Title:  c.Original.Title,
				Slug:   c.Original.Slug,
				Status: c.Original.Status,
			},
		}
		return nil
	})
}

func countPostPerStatus(ctx context.Context, q *query.CountPostPerStatus) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {

		type dbStatusCount struct {
			Status enum.PostStatus `db:"status"`
			Count  int             `db:"count"`
		}

		q.Result = make(map[enum.PostStatus]int)
		stats := []*dbStatusCount{}
		err := trx.Select(&stats, "SELECT status, COUNT(*) AS count FROM posts WHERE tenant_id = $1 GROUP BY status", tenant.ID)
		if err != nil {
			return errors.Wrap(err, "failed to count posts per status")
		}

		for _, v := range stats {
			q.Result[v.Status] = v.Count
		}
		return nil
	})
}

func addNewPost(ctx context.Context, c *cmd.AddNewPost) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		isApproved := !tenant.IsModerationEnabled || !user.RequiresModeration()
		var id int
		// Detect language using lingua-go
		lang := detectPostLanguage(c.Title, c.Description)

		err := trx.Get(&id,
			`INSERT INTO posts (title, slug, number, description, tenant_id, user_id, created_at, status, is_approved, language)
			 VALUES ($1, $2, (SELECT COALESCE(MAX(number), 0) + 1 FROM posts p WHERE p.tenant_id = $4), $3, $4, $5, $6, 0, $7, $8)
			 RETURNING id`, c.Title, slug.Make(c.Title), c.Description, tenant.ID, user.ID, time.Now(), isApproved, lang)
		if err != nil {
			return errors.Wrap(err, "failed add new post")
		}

		q := &query.GetPostByID{PostID: id}
		if err := getPostByID(ctx, q); err != nil {
			return err
		}
		c.Result = q.Result

		if err := internalAddSubscriber(trx, q.Result, tenant, user, false); err != nil {
			return err
		}

		return nil
	})
}

func updatePost(ctx context.Context, c *cmd.UpdatePost) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		// Detect language using lingua-go
		lang := detectPostLanguage(c.Title, c.Description)
		_, err := trx.Execute(`UPDATE posts SET title = $1, slug = $2, description = $3, language = $4
								 WHERE id = $5 AND tenant_id = $6`, c.Title, slug.Make(c.Title), c.Description, lang, c.Post.ID, tenant.ID)

		if err != nil {
			return errors.Wrap(err, "failed update post")
		}

		q := &query.GetPostByID{PostID: c.Post.ID}
		if err := getPostByID(ctx, q); err != nil {
			return err
		}
		c.Result = q.Result
		return nil
	})
}

// detectPostLanguage uses lingua-go to detect the language of a post and maps it to a PostgreSQL tsvector config or 'simple'.
// All language mappings are centralized in app/models/enum/locale.go
func detectPostLanguage(title, description string) string {
	// Get all supported lingua languages from the centralized locale enum
	linguaLangs := enum.GetLinguaLanguages()
	detector := lingua.NewLanguageDetectorBuilder().FromLanguages(linguaLangs...).Build()
	text := strings.TrimSpace(title + " " + description)
	lang, exists := detector.DetectLanguageOf(text)
	if !exists {
		return "simple"
	}

	locale, ok := enum.GetLocaleByLinguaLanguage(lang)
	if !ok {
		return "simple"
	}

	// Map to PostgreSQL tsvector config using the locale's PostgresConfig
	return locale.PostgresConfig
}

func getPostByID(ctx context.Context, q *query.GetPostByID) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		post, err := querySinglePost(ctx, trx, buildSinglePostQuery(user, "p.tenant_id = $1 AND p.id = $2"), tenant.ID, q.PostID)
		if err != nil {
			return errors.Wrap(err, "failed to get post with id '%d'", q.PostID)
		}
		q.Result = post
		return nil
	})
}

func getPostBySlug(ctx context.Context, q *query.GetPostBySlug) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		post, err := querySinglePost(ctx, trx, buildSinglePostQuery(user, "p.tenant_id = $1 AND p.slug = $2"), tenant.ID, q.Slug)
		if err != nil {
			return errors.Wrap(err, "failed to get post with slug '%s'", q.Slug)
		}
		q.Result = post
		return nil
	})
}

func getPostByNumber(ctx context.Context, q *query.GetPostByNumber) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		post, err := querySinglePost(ctx, trx, buildSinglePostQuery(user, "p.tenant_id = $1 AND p.number = $2"), tenant.ID, q.Number)
		if err != nil {
			return errors.Wrap(err, "failed to get post with number '%d'", q.Number)
		}
		q.Result = post
		return nil
	})
}

func preprocessSearchQuery(query string) string {
	// Common noise words that don't add search value

	noiseWords := env.SearchNoiseWords()

	words := strings.Fields(strings.ToLower(query))
	var filteredWords []string

	for _, word := range words {
		isNoise := false
		for _, noise := range noiseWords {
			if word == noise {
				isNoise = true
				break
			}
		}
		if !isNoise && len(word) > 2 { // Also filter very short words
			filteredWords = append(filteredWords, word)
		}
	}

	return strings.Join(filteredWords, " ")
}

func findSimilarPosts(ctx context.Context, q *query.FindSimilarPosts) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		innerQuery := buildPostQuery(user, "p.tenant_id = $1 AND p.status = ANY($2)", "")

		filteredQuery := preprocessSearchQuery(q.Query)

		var (
			posts []*dbEntities.Post
			err   error
		)

		if filteredQuery == "" {
			q.Result = make([]*entity.Post, 0)
		} else {
			tsConfig := MapLocaleToTSConfig(tenant.Locale)

			// Build tsquery with AND operator between words and prefix matching on each word
			// The search column already contains both language-specific and simple tsvectors
			tsQueryExpr := fmt.Sprintf("to_tsquery('%s', regexp_replace(regexp_replace($3, '\\\\s+', ':* & ', 'g'), '$', ':*'))", tsConfig)
			tsQuerySimple := "to_tsquery('simple', regexp_replace(regexp_replace($3, '\\\\s+', ':* & ', 'g'), '$', ':*'))"

			// Use ts_rank_cd (cover density ranking) for better relevance scoring
			// Query against the generated search column which combines language-specific and simple tsvectors
			score := fmt.Sprintf("ts_rank_cd(q.search, %s) + ts_rank_cd(q.search, %s)", tsQueryExpr, tsQuerySimple)

			// Match against the pre-computed search column
			whereParts := fmt.Sprintf(`q.search @@ %s OR q.search @@ %s`, tsQueryExpr, tsQuerySimple)

			sql := fmt.Sprintf(`
				SELECT * FROM (%s) AS q
				WHERE %s
				ORDER BY %s DESC
				LIMIT 5
			`, innerQuery, whereParts, score)
			err = trx.Select(&posts, sql, tenant.ID, pq.Array([]enum.PostStatus{
				enum.PostOpen,
				enum.PostStarted,
				enum.PostPlanned,
				enum.PostCompleted,
				enum.PostDeclined,
			}), ToTSQuery(SanitizeString(q.Query)))
		}
		if err != nil {
			return errors.Wrap(err, "failed to find similar posts")
		}

		q.Result = make([]*entity.Post, len(posts))
		for i, post := range posts {
			q.Result[i] = post.ToModel(ctx)
		}
		return nil
	})
}

func searchPosts(ctx context.Context, q *query.SearchPosts) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		innerQuery := buildPostQuery(user, "p.tenant_id = $1 AND p.status = ANY($2)", q.ModerationFilter)

		if q.Tags == nil {
			q.Tags = []string{}
		}

		if q.Statuses == nil {
			q.Statuses = []enum.PostStatus{}
		}

		if q.Limit != "all" {
			if _, err := strconv.Atoi(q.Limit); err != nil {
				q.Limit = "30"
			}
		}

		var (
			posts []*dbEntities.Post
			err   error
		)
		if q.Query != "" {
			tsQuery := ToTSQuery(SanitizeString(q.Query))
			if tsQuery == "" {
				q.Result = make([]*entity.Post, 0)
				return nil
			}

			tsConfig := MapLocaleToTSConfig(tenant.Locale)

			tsQueryExpr := fmt.Sprintf("to_tsquery('%s', regexp_replace(regexp_replace($3, '\\\\s+', ':* & ', 'g'), '$', ':*'))", tsConfig)
			tsQuerySimple := "to_tsquery('simple', regexp_replace(regexp_replace($3, '\\\\s+', ':* & ', 'g'), '$', ':*'))"

			score := fmt.Sprintf("ts_rank_cd(q.search, %s) + ts_rank_cd(q.search, %s)", tsQueryExpr, tsQuerySimple)

			searchPredicate := fmt.Sprintf(`q.search @@ %s OR q.search @@ %s`, tsQueryExpr, tsQuerySimple)

			condition, statuses, _ := getViewData(*q, 4)

			if q.MyPostsOnly && user != nil {
				condition += " AND user_id = " + strconv.Itoa(user.ID)
			}

			sql := fmt.Sprintf(`
				SELECT * FROM (%s) AS q
				WHERE (%s) %s
				ORDER BY %s DESC
				LIMIT %s
			`, innerQuery, searchPredicate, condition, score, q.Limit)

			params := []interface{}{tenant.ID, pq.Array(statuses), tsQuery}
			if len(q.Tags) > 0 && !q.NoTagsOnly {
				params = append(params, pq.Array(q.Tags))
			}
			err = trx.Select(&posts, sql, params...)
		} else {
			condition, statuses, sort := getViewData(*q, 3)

			if q.MyPostsOnly && user != nil {
				condition += " AND user_id = " + strconv.Itoa(user.ID)
			}

			sql := fmt.Sprintf(`
				SELECT * FROM (%s) AS q
				WHERE 1 = 1 %s
				ORDER BY %s DESC
				LIMIT %s
			`, innerQuery, condition, sort, q.Limit)
			params := []interface{}{tenant.ID, pq.Array(statuses)}
			if len(q.Tags) > 0 && !q.NoTagsOnly {
				params = append(params, pq.Array(q.Tags))
			}
			err = trx.Select(&posts, sql, params...)
		}

		if err != nil {
			return errors.Wrap(err, "failed to search posts")
		}

		q.Result = make([]*entity.Post, len(posts))
		for i, post := range posts {
			q.Result[i] = post.ToModel(ctx)
		}
		return nil
	})
}

func getAllPosts(ctx context.Context, q *query.GetAllPosts) error {
	return using(ctx, func(trx *dbx.Trx, tenant *entity.Tenant, user *entity.User) error {
		searchQuery := &query.SearchPosts{View: "all", Limit: "all"}
		if err := searchPosts(ctx, searchQuery); err != nil {
			return errors.Wrap(err, "failed to get all posts")
		}
		q.Result = searchQuery.Result
		return nil
	})
}

func querySinglePost(ctx context.Context, trx *dbx.Trx, query string, args ...any) (*entity.Post, error) {
	post := dbEntities.Post{}

	if err := trx.Get(&post, query, args...); err != nil {
		return nil, err
	}

	return post.ToModel(ctx), nil
}

func buildPostQuery(user *entity.User, filter string, moderationFilter string) string {
	tagCondition := `AND tags.is_public = true`
	if user != nil && user.IsCollaborator() {
		tagCondition = ``
	}
	hasVotedSubQuery := "null"
	if user != nil {
		hasVotedSubQuery = fmt.Sprintf("(SELECT true FROM post_votes WHERE post_id = p.id AND user_id = %d)", user.ID)
	}

	// Add approval filtering based on moderation filter and user permissions
	approvalFilter := ""

	// If user is a collaborator and has specified a moderation filter, apply it
	if user != nil && user.IsCollaborator() && moderationFilter != "" {
		switch moderationFilter {
		case "pending":
			// Show only unapproved posts
			approvalFilter = " AND p.is_approved = false"
		case "approved":
			// Show only approved posts
			approvalFilter = " AND p.is_approved = true"
		}
		// If moderationFilter is neither "pending" nor "approved", show all posts (no filter)
	} else if user != nil {
		// Regular authenticated users can see approved posts + their own unapproved posts
		approvalFilter = fmt.Sprintf(" AND (p.is_approved = true OR p.user_id = %d)", user.ID)
	} else {
		// Anonymous users can only see approved posts
		approvalFilter = " AND p.is_approved = true"
	}

	combinedFilter := filter + approvalFilter
	return fmt.Sprintf(sqlSelectPostsWhere, tagCondition, hasVotedSubQuery, combinedFilter)
}

// buildSinglePostQuery is used for fetching individual posts (by ID, slug, or number)
// Collaborators can view any post for moderation purposes
func buildSinglePostQuery(user *entity.User, filter string) string {
	tagCondition := `AND tags.is_public = true`
	if user != nil && user.IsCollaborator() {
		tagCondition = ``
	}
	hasVotedSubQuery := "null"
	if user != nil {
		hasVotedSubQuery = fmt.Sprintf("(SELECT true FROM post_votes WHERE post_id = p.id AND user_id = %d)", user.ID)
	}

	// Approval filtering for single post views
	approvalFilter := ""
	if user != nil && user.IsCollaborator() {
		// Collaborators can view any post (for moderation purposes)
		approvalFilter = ""
	} else if user != nil {
		// Regular authenticated users can see approved posts + their own unapproved posts
		approvalFilter = fmt.Sprintf(" AND (p.is_approved = true OR p.user_id = %d)", user.ID)
	} else {
		// Anonymous users can only see approved posts
		approvalFilter = " AND p.is_approved = true"
	}

	combinedFilter := filter + approvalFilter
	return fmt.Sprintf(sqlSelectPostsWhere, tagCondition, hasVotedSubQuery, combinedFilter)
}