hako/internal/archival/store/search_builder.go

package store

import (
	"strings"

	"github.com/huandu/go-sqlbuilder"
)

// sanitizeFTS5QueryForParam prepares a query for use as a parameter (without quotes)
func sanitizeFTS5QueryForParam(query string) string {
	if query == "" {
		return ""
	}
	// For parameterized queries, escape quotes but don't wrap in quotes
	// FTS5 will handle the query format when used as a parameter
	return strings.ReplaceAll(query, `"`, `""`)
}

// searchBuilder builds SQL queries for link searching using go-sqlbuilder.
// This pattern ensures reliable argument ordering and makes it easy to add
// new filter types without fragile manual argument manipulation.
type searchBuilder struct {
	// For non-FTS queries, we use a simple SelectBuilder
	sb *sqlbuilder.SelectBuilder

	// For FTS queries, we need CTE support
	needsFTS    bool
	ftsQuery    string
	ftsUserID   string
	likePattern string

	// Track filters to apply
	userID     string
	categoryID string
	labelIDs   []string
}

// newSearchBuilder creates a new searchBuilder instance
func newSearchBuilder() *searchBuilder {
	return &searchBuilder{
		sb: sqlbuilder.NewSelectBuilder(),
	}
}

// addUserFilter adds a filter for the user ID (required)
func (b *searchBuilder) addUserFilter(userID string) {
	b.userID = userID
}

// addCategoryFilter adds a filter for category ID (optional)
func (b *searchBuilder) addCategoryFilter(categoryID string) {
	b.categoryID = categoryID
}

// addSearchFilter adds full-text search across URL, title, and content
func (b *searchBuilder) addSearchFilter(query, userID string) {
	if query == "" {
		return
	}
	b.needsFTS = true
	b.likePattern = "%" + query + "%"
	// Store raw query for parameterized FTS5 query
	b.ftsQuery = query
	b.ftsUserID = userID
}

// addLabelsFilter adds a filter for label IDs (optional, for future use)
func (b *searchBuilder) addLabelsFilter(labelIDs []string) {
	b.labelIDs = labelIDs
}

// buildSelectQuery builds a complete SELECT query for listing links
func (b *searchBuilder) buildSelectQuery(limit, offset int) (string, []any) {
	if b.needsFTS {
		return b.buildFTSSelectQuery(limit, offset)
	}
	return b.buildSimpleSelectQuery(limit, offset)
}

// buildCountQuery builds a complete SELECT COUNT query
func (b *searchBuilder) buildCountQuery() (string, []any) {
	if b.needsFTS {
		return b.buildFTSCountQuery()
	}
	return b.buildSimpleCountQuery()
}

// buildSimpleSelectQuery builds a SELECT query without FTS
func (b *searchBuilder) buildSimpleSelectQuery(limit, offset int) (string, []any) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("l.id", "l.url", "l.user_id", "l.total_size", "l.created_at", "l.updated_at")
	sb.From("links l")

	b.applyFilters(sb)

	sb.OrderBy("l.created_at DESC")
	sb.Limit(limit)
	sb.Offset(offset)

	query, args := sb.Build()
	return query, toAnySlice(args)
}

// buildSimpleCountQuery builds a COUNT query without FTS
func (b *searchBuilder) buildSimpleCountQuery() (string, []any) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("COUNT(DISTINCT l.id)")
	sb.From("links l")

	b.applyFilters(sb)

	query, args := sb.Build()
	return query, toAnySlice(args)
}

// buildFTSSelectQuery builds a SELECT query with FTS using CTE
func (b *searchBuilder) buildFTSSelectQuery(limit, offset int) (string, []any) {
	// Build FTS CTE subquery
	ftsSb := sqlbuilder.NewSelectBuilder()
	ftsSb.Distinct()
	ftsSb.Select("a.link_id")
	ftsSb.From("archive_files_fts fts")
	ftsSb.JoinWithOption(sqlbuilder.InnerJoin, "archive_files af", "fts.id = af.id")
	ftsSb.JoinWithOption(sqlbuilder.InnerJoin, "archives a", "af.archive_id = a.id")
	// Use parameterized query for FTS5 MATCH
	// FTS5 supports parameterized queries, and we sanitize the query for safety
	ftsQueryParam := sanitizeFTS5QueryForParam(b.ftsQuery)
	ftsSb.Where("fts.content MATCH " + ftsSb.Var(ftsQueryParam))
	ftsSb.Where(ftsSb.Equal("a.user_id", b.ftsUserID))

	// Create CTE
	cteQuery := sqlbuilder.CTEQuery("fts_matches").As(ftsSb)

	// Build main SELECT using the CTE
	mainSb := sqlbuilder.With(cteQuery).Select(
		"DISTINCT l.id", "l.url", "l.user_id", "l.total_size", "l.created_at", "l.updated_at",
	)
	mainSb.From("links l")

	// Add joins for search
	mainSb.JoinWithOption(sqlbuilder.LeftJoin, "archives a_title", "l.id = a_title.link_id")
	mainSb.JoinWithOption(sqlbuilder.LeftJoin, "fts_matches fm", "l.id = fm.link_id")

	// Apply standard filters
	b.applyFilters(mainSb)

	// Add search conditions (URL, title, or FTS match)
	urlCond := mainSb.Like("l.url", b.likePattern)
	titleCond := mainSb.And(mainSb.IsNotNull("a_title.title"), mainSb.Like("a_title.title", b.likePattern))
	ftsCond := mainSb.IsNotNull("fm.link_id")
	mainSb.Where(mainSb.Or(urlCond, titleCond, ftsCond))

	mainSb.OrderBy("l.created_at DESC")
	mainSb.Limit(limit)
	mainSb.Offset(offset)

	// Build main query - sqlbuilder.With should handle CTE args automatically
	query, args := mainSb.Build()
	return query, toAnySlice(args)
}

// buildFTSCountQuery builds a COUNT query with FTS using CTE
func (b *searchBuilder) buildFTSCountQuery() (string, []any) {
	// Build FTS CTE subquery
	ftsSb := sqlbuilder.NewSelectBuilder()
	ftsSb.Distinct()
	ftsSb.Select("a.link_id")
	ftsSb.From("archive_files_fts fts")
	ftsSb.JoinWithOption(sqlbuilder.InnerJoin, "archive_files af", "fts.id = af.id")
	ftsSb.JoinWithOption(sqlbuilder.InnerJoin, "archives a", "af.archive_id = a.id")
	// Use parameterized query for FTS5 MATCH
	// FTS5 supports parameterized queries, and we sanitize the query for safety
	ftsQueryParam := sanitizeFTS5QueryForParam(b.ftsQuery)
	ftsSb.Where("fts.content MATCH " + ftsSb.Var(ftsQueryParam))
	ftsSb.Where(ftsSb.Equal("a.user_id", b.ftsUserID))

	// Create CTE
	cteQuery := sqlbuilder.CTEQuery("fts_matches").As(ftsSb)

	// Build main SELECT using the CTE
	mainSb := sqlbuilder.With(cteQuery).Select("COUNT(DISTINCT l.id)")
	mainSb.From("links l")

	// Add joins for search
	mainSb.JoinWithOption(sqlbuilder.LeftJoin, "archives a_title", "l.id = a_title.link_id")
	mainSb.JoinWithOption(sqlbuilder.LeftJoin, "fts_matches fm", "l.id = fm.link_id")

	// Apply standard filters
	b.applyFilters(mainSb)

	// Add search conditions
	urlCond := mainSb.Like("l.url", b.likePattern)
	titleCond := mainSb.And(mainSb.IsNotNull("a_title.title"), mainSb.Like("a_title.title", b.likePattern))
	ftsCond := mainSb.IsNotNull("fm.link_id")
	mainSb.Where(mainSb.Or(urlCond, titleCond, ftsCond))

	// Build main query - sqlbuilder.With should handle CTE args automatically
	query, args := mainSb.Build()
	return query, toAnySlice(args)
}

// applyFilters applies common filters (user, category, labels) to a SelectBuilder
func (b *searchBuilder) applyFilters(sb *sqlbuilder.SelectBuilder) {
	// User filter (required)
	if b.userID != "" {
		sb.Where(sb.Equal("l.user_id", b.userID))
	}

	// Category filter (optional)
	if b.categoryID != "" {
		sb.JoinWithOption(sqlbuilder.InnerJoin, "link_categories lc", "l.id = lc.link_id")
		sb.Where(sb.Equal("lc.category_id", b.categoryID))
	}

	// Labels filter (optional, for future use)
	if len(b.labelIDs) > 0 {
		sb.JoinWithOption(sqlbuilder.InnerJoin, "link_labels ll", "l.id = ll.link_id")
		sb.Where(sb.In("ll.label_id", toInterfaceSlice(b.labelIDs)...))
	}
}

// toAnySlice converts []interface{} to []any
func toAnySlice(args []interface{}) []any {
	result := make([]any, len(args))
	copy(result, args)
	return result
}

// toInterfaceSlice converts []string to []interface{} for sqlbuilder
func toInterfaceSlice(s []string) []interface{} {
	result := make([]interface{}, len(s))
	for i, v := range s {
		result[i] = v
	}
	return result
}