job-search/internal/store/search.go

package store

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

	"github.com/jmoiron/sqlx"

	"git.avsdev.uk/Go/job-search/internal/search"
)

// ftsColumns are the opportunities_fts columns each field's terms search,
// as an FTS5 column filter.
var ftsColumns = map[search.Field]string{
	search.FieldText:  "{title description}",
	search.FieldTitle: "title",
	search.FieldBuyer: "buyer_name",
}

// anyOfFields are the fields whose terms match if any of them does, in the
// order their clauses are added.
var anyOfFields = []search.Field{search.FieldTitle, search.FieldBuyer}

// searchClauses holds a search's terms, sorted by how they combine.
type searchClauses struct {
	// required are full-text matches (words without a prompt) that must
	// all match.
	required []string
	// anyOf are full-text matches by field, of which one must match.
	anyOf map[search.Field][]string
	// excluded are full-text matches of which none may match.
	excluded []string

	buyerIDs         []string
	excludedBuyerIDs []string

	// cpv and excludedCPV are conditions on one CPV code (searchCPV*
	// fragments), with their arguments.
	cpv             []string
	cpvArgs         []any
	excludedCPV     []string
	excludedCPVArgs []any
}

// SearchOpportunities returns the UUIDs of the opportunities matching
// query, in no particular order and never nil. Unless releasedFrom is zero,
// only opportunities whose latest release is on or after it are returned
// (as OpportunityFilter.ReleasedFrom). A query with no terms matches every
// opportunity.
func (s *Store) SearchOpportunities(
	ctx context.Context, query search.Query, releasedFrom time.Time,
) ([]string, error) {
	statement, args := searchOpportunitiesQuery(query, releasedFrom)
	statement, args, err := sqlx.In(statement, args...)
	if err != nil {
		return nil, fmt.Errorf("expanding the search query: %w", err)
	}

	uuids := []string{}
	err = s.db.SelectContext(ctx, &uuids, s.db.Rebind(statement), args...)
	if err != nil {
		return nil, fmt.Errorf("searching opportunities: %w", err)
	}

	return uuids, nil
}

// searchOpportunitiesQuery assembles the SearchOpportunities query from the
// fragments in queries.go, with its arguments. It must be expanded by
// sqlx.In.
func searchOpportunitiesQuery(
	query search.Query, releasedFrom time.Time,
) (string, []any) {
	clauses := sortTerms(query.Terms)
	var where []string
	var args []any
	add := func(clause string, arguments ...any) {
		where = append(where, clause)
		args = append(args, arguments...)
	}

	if len(clauses.required) > 0 {
		add(searchOpportunityText, strings.Join(clauses.required, " AND "))
	}
	for _, field := range anyOfFields {
		if matches := clauses.anyOf[field]; len(matches) > 0 {
			add(searchOpportunityText, strings.Join(matches, " OR "))
		}
	}
	if len(clauses.excluded) > 0 {
		add(
			"NOT "+searchOpportunityText,
			strings.Join(clauses.excluded, " OR "),
		)
	}

	if len(clauses.buyerIDs) > 0 {
		add(searchOpportunityBuyerID, clauses.buyerIDs)
	}
	if len(clauses.excludedBuyerIDs) > 0 {
		add("NOT "+searchOpportunityBuyerID, clauses.excludedBuyerIDs)
	}

	if clause, arguments := clauses.cpvClause(); clause != "" {
		add(clause, arguments...)
	}

	if !releasedFrom.IsZero() {
		add(filterOpportunityReleasedFrom, releasedFrom.UTC())
	}

	statement := searchOpportunitiesSelect
	if len(where) > 0 {
		statement += "\nWHERE " + strings.Join(where, "\n\tAND ")
	}

	return statement, args
}

// sortTerms sorts terms by how they combine into the query.
func sortTerms(terms []search.Term) searchClauses {
	clauses := searchClauses{
		anyOf: map[search.Field][]string{},
	}
	for _, term := range terms {
		switch {
		case term.Field == search.FieldBuyerID && term.Negated:
			clauses.excludedBuyerIDs = append(
				clauses.excludedBuyerIDs, term.Value,
			)
		case term.Field == search.FieldBuyerID:
			clauses.buyerIDs = append(clauses.buyerIDs, term.Value)
		case term.Field == search.FieldCPV && term.Negated:
			condition, argument := cpvCondition(term)
			clauses.excludedCPV = append(clauses.excludedCPV, condition)
			clauses.excludedCPVArgs = append(
				clauses.excludedCPVArgs, argument,
			)
		case term.Field == search.FieldCPV:
			condition, argument := cpvCondition(term)
			clauses.cpv = append(clauses.cpv, condition)
			clauses.cpvArgs = append(clauses.cpvArgs, argument)
		case term.Negated:
			clauses.excluded = append(clauses.excluded, textMatch(term))
		case term.Field == search.FieldText:
			clauses.required = append(clauses.required, textMatch(term))
		default:
			clauses.anyOf[term.Field] = append(
				clauses.anyOf[term.Field], textMatch(term),
			)
		}
	}

	return clauses
}

// cpvClause returns the clause for the CPV terms, with its arguments, or ""
// if there are none. With cpv: terms, an opportunity needs a code that one
// of them matches and no !cpv: term does; with only !cpv: terms, it needs
// no code that any of them matches.
func (c searchClauses) cpvClause() (string, []any) {
	excluded := "(" + strings.Join(c.excludedCPV, " OR ") + ")"
	switch {
	case len(c.cpv) > 0 && len(c.excludedCPV) > 0:
		condition := "(" + strings.Join(c.cpv, " OR ") + ")" +
			" AND NOT " + excluded
		return fmt.Sprintf(searchOpportunityCPV, condition),
			append(append([]any{}, c.cpvArgs...), c.excludedCPVArgs...)
	case len(c.cpv) > 0:
		condition := "(" + strings.Join(c.cpv, " OR ") + ")"
		return fmt.Sprintf(searchOpportunityCPV, condition), c.cpvArgs
	case len(c.excludedCPV) > 0:
		return "NOT " + fmt.Sprintf(searchOpportunityCPV, excluded),
			c.excludedCPVArgs
	}

	return "", nil
}

// cpvCondition returns the condition on one CPV code for a cpv: term, with
// its argument: a code prefix, or a full-text search of its description.
func cpvCondition(term search.Term) (string, any) {
	if code, ok := term.CPVCode(); ok {
		return searchCPVCodePrefix, code + "%"
	}
	return searchCPVDescription, ftsPhrase(term.Words())
}

// textMatch returns the FTS5 query of opportunities_fts for a full-text
// term: its words as a phrase, in the columns its field searches.
func textMatch(term search.Term) string {
	return ftsColumns[term.Field] + " : " + ftsPhrase(term.Words())
}

// ftsPhrase returns an FTS5 phrase of words, the last also matching as a
// prefix. The words must be split as search.Term.Words splits them, so
// they hold no quotes or FTS5 syntax.
func ftsPhrase(words []string) string {
	return `"` + strings.Join(words, " ") + `"*`
}