navidrome/persistence/criteria_sql.go

package persistence

import (
	"errors"
	"fmt"
	"maps"
	"reflect"
	"slices"
	"strconv"
	"strings"
	"time"

	"github.com/Masterminds/squirrel"
	"github.com/navidrome/navidrome/model"
	"github.com/navidrome/navidrome/model/criteria"
)

type smartPlaylistJoinType int

const (
	smartPlaylistJoinNone            smartPlaylistJoinType = 0
	smartPlaylistJoinAlbumAnnotation smartPlaylistJoinType = 1 << iota
	smartPlaylistJoinArtistAnnotation
)

func (j smartPlaylistJoinType) has(other smartPlaylistJoinType) bool {
	return j&other != 0
}

type smartPlaylistField struct {
	expr            string
	order           string
	joinType        smartPlaylistJoinType
	emptyValues     []string // additional values that encode "missing" for string columns (e.g. '[]' for lyrics)
	coalesceDefault any      // missing-row default for a nullable annotation column; nil = none. See annotationCond.
}

type smartPlaylistCriteria struct {
	criteria.Criteria
	owner model.User
}

func newSmartPlaylistCriteria(c criteria.Criteria, opts ...func(*smartPlaylistCriteria)) smartPlaylistCriteria {
	cSQL := smartPlaylistCriteria{Criteria: c}
	for _, opt := range opts {
		opt(&cSQL)
	}
	return cSQL
}

func withSmartPlaylistOwner(owner model.User) func(*smartPlaylistCriteria) {
	return func(c *smartPlaylistCriteria) {
		c.owner = owner
	}
}

var smartPlaylistFields = map[string]smartPlaylistField{
	"title":                {expr: "media_file.title"},
	"album":                {expr: "media_file.album"},
	"hascoverart":          {expr: "media_file.has_cover_art"},
	"tracknumber":          {expr: "media_file.track_number"},
	"discnumber":           {expr: "media_file.disc_number"},
	"year":                 {expr: "media_file.year"},
	"date":                 {expr: "media_file.date"},
	"originalyear":         {expr: "media_file.original_year"},
	"originaldate":         {expr: "media_file.original_date"},
	"releaseyear":          {expr: "media_file.release_year"},
	"releasedate":          {expr: "media_file.release_date"},
	"size":                 {expr: "media_file.size"},
	"compilation":          {expr: "media_file.compilation"},
	"missing":              {expr: "media_file.missing"},
	"explicitstatus":       {expr: "media_file.explicit_status"},
	"dateadded":            {expr: "media_file.created_at"},
	"datemodified":         {expr: "media_file.updated_at"},
	"discsubtitle":         {expr: "media_file.disc_subtitle"},
	"comment":              {expr: "media_file.comment"},
	"lyrics":               {expr: "media_file.lyrics", emptyValues: []string{"[]"}},
	"sorttitle":            {expr: "media_file.sort_title"},
	"sortalbum":            {expr: "media_file.sort_album_name"},
	"sortartist":           {expr: "media_file.sort_artist_name"},
	"sortalbumartist":      {expr: "media_file.sort_album_artist_name"},
	"albumcomment":         {expr: "media_file.mbz_album_comment"},
	"catalognumber":        {expr: "media_file.catalog_num"},
	"filepath":             {expr: "media_file.path"},
	"filetype":             {expr: "media_file.suffix"},
	"codec":                {expr: "media_file.codec"},
	"duration":             {expr: "media_file.duration"},
	"bitrate":              {expr: "media_file.bit_rate"},
	"bitdepth":             {expr: "media_file.bit_depth"},
	"samplerate":           {expr: "media_file.sample_rate"},
	"bpm":                  {expr: "media_file.bpm"},
	"channels":             {expr: "media_file.channels"},
	"loved":                {expr: "annotation.starred", coalesceDefault: false},
	"dateloved":            {expr: "annotation.starred_at"},
	"lastplayed":           {expr: "annotation.play_date"},
	"daterated":            {expr: "annotation.rated_at"},
	"playcount":            {expr: "annotation.play_count", coalesceDefault: 0},
	"rating":               {expr: "annotation.rating", coalesceDefault: 0},
	"averagerating":        {expr: "media_file.average_rating"},
	"albumrating":          {expr: "album_annotation.rating", coalesceDefault: 0, joinType: smartPlaylistJoinAlbumAnnotation},
	"albumloved":           {expr: "album_annotation.starred", coalesceDefault: false, joinType: smartPlaylistJoinAlbumAnnotation},
	"albumplaycount":       {expr: "album_annotation.play_count", coalesceDefault: 0, joinType: smartPlaylistJoinAlbumAnnotation},
	"albumlastplayed":      {expr: "album_annotation.play_date", joinType: smartPlaylistJoinAlbumAnnotation},
	"albumdateloved":       {expr: "album_annotation.starred_at", joinType: smartPlaylistJoinAlbumAnnotation},
	"albumdaterated":       {expr: "album_annotation.rated_at", joinType: smartPlaylistJoinAlbumAnnotation},
	"artistrating":         {expr: "artist_annotation.rating", coalesceDefault: 0, joinType: smartPlaylistJoinArtistAnnotation},
	"artistloved":          {expr: "artist_annotation.starred", coalesceDefault: false, joinType: smartPlaylistJoinArtistAnnotation},
	"artistplaycount":      {expr: "artist_annotation.play_count", coalesceDefault: 0, joinType: smartPlaylistJoinArtistAnnotation},
	"artistlastplayed":     {expr: "artist_annotation.play_date", joinType: smartPlaylistJoinArtistAnnotation},
	"artistdateloved":      {expr: "artist_annotation.starred_at", joinType: smartPlaylistJoinArtistAnnotation},
	"artistdaterated":      {expr: "artist_annotation.rated_at", joinType: smartPlaylistJoinArtistAnnotation},
	"mbz_album_id":         {expr: "media_file.mbz_album_id"},
	"mbz_album_artist_id":  {expr: "media_file.mbz_album_artist_id"},
	"mbz_artist_id":        {expr: "media_file.mbz_artist_id"},
	"mbz_recording_id":     {expr: "media_file.mbz_recording_id"},
	"mbz_release_track_id": {expr: "media_file.mbz_release_track_id"},
	"mbz_release_group_id": {expr: "media_file.mbz_release_group_id"},
	"rgalbumgain":          {expr: "media_file.rg_album_gain"},
	"rgalbumpeak":          {expr: "media_file.rg_album_peak"},
	"rgtrackgain":          {expr: "media_file.rg_track_gain"},
	"rgtrackpeak":          {expr: "media_file.rg_track_peak"},
	"library_id":           {expr: "media_file.library_id"},
	"random":               {order: "random()"},
}

func (c smartPlaylistCriteria) Where() (squirrel.Sqlizer, error) {
	if c.Criteria.Expression == nil {
		return squirrel.Expr("1 = 1"), nil
	}
	return c.exprSQL(c.Criteria.Expression)
}

func (c smartPlaylistCriteria) exprSQL(expr criteria.Expression) (squirrel.Sqlizer, error) {
	switch e := expr.(type) {
	case criteria.All:
		and := squirrel.And{}
		for _, child := range e {
			cond, err := c.exprSQL(child)
			if err != nil {
				return nil, err
			}
			and = append(and, cond)
		}
		return mergeNegatedJsonConds(and), nil
	case criteria.Any:
		or := squirrel.Or{}
		for _, child := range e {
			cond, err := c.exprSQL(child)
			if err != nil {
				return nil, err
			}
			or = append(or, cond)
		}
		return mergeJsonConds(or), nil
	case criteria.Is:
		return comparisonExpr(e, cmpEq)
	case criteria.IsNot:
		return isNotExpr(e)
	case criteria.Gt:
		return comparisonExpr(e, cmpGt)
	case criteria.Lt:
		return comparisonExpr(e, cmpLt)
	case criteria.Before:
		return comparisonExpr(e, cmpLt)
	case criteria.After:
		return comparisonExpr(e, cmpGt)
	case criteria.Contains:
		return likeExpr(e, "%%%v%%", false)
	case criteria.NotContains:
		return likeExpr(e, "%%%v%%", true)
	case criteria.StartsWith:
		return likeExpr(e, "%v%%", false)
	case criteria.EndsWith:
		return likeExpr(e, "%%%v", false)
	case criteria.InTheRange:
		return rangeExpr(e)
	case criteria.InTheLast:
		return periodExpr(e, false)
	case criteria.NotInTheLast:
		return periodExpr(e, true)
	case criteria.InPlaylist:
		return c.inList(e, false)
	case criteria.NotInPlaylist:
		return c.inList(e, true)
	case criteria.IsMissing:
		return missingExpr(e, true)
	case criteria.IsPresent:
		return missingExpr(e, false)
	default:
		return nil, fmt.Errorf("unknown criteria expression type %T", expr)
	}
}

func isNotExpr(values map[string]any) (squirrel.Sqlizer, error) {
	if _, value, info, ok := singleField(values); ok && (info.IsTag || info.IsRole) {
		return jsonExpr(info, squirrel.Eq{"value": value}, true), nil
	}
	return comparisonExpr(values, cmpNe)
}

func missingExpr(values map[string]any, checkAbsence bool) (squirrel.Sqlizer, error) {
	field, value, info, ok := singleField(values)
	if !ok {
		if len(values) != 1 {
			return nil, fmt.Errorf("invalid field in criteria: isMissing/isPresent requires exactly one field")
		}
		return nil, fmt.Errorf("invalid field in criteria: %s", field)
	}
	b, ok := value.(bool)
	if !ok {
		return nil, fmt.Errorf("invalid boolean value for 'missing' expression: %s: %v", field, value)
	}
	negate := checkAbsence == b

	switch {
	case info.IsTag || info.IsRole:
		return jsonExpr(info, nil, negate), nil
	case info.Nullable:
		// Nullable column fields are stored in dedicated columns, not in the tags JSON, so
		// "missing" maps to a column check rather than a json_tree lookup. Numeric/boolean
		// columns (e.g. ReplayGain, BPM) encode absence as NULL only; string columns (e.g.
		// mbz_* IDs, lyrics) additionally treat empty string — and any field-specific empty
		// encodings (e.g. '[]' for lyrics) — as missing. The unified flow below handles both:
		// numeric/boolean fields simply have no empties, so the loops are no-ops.
		f, ok := smartPlaylistFields[info.Name()]
		if !ok || f.expr == "" {
			return nil, fmt.Errorf("invalid field in criteria: %s", field)
		}
		col := f.expr
		var empties []string
		if !info.Numeric && !info.Boolean {
			empties = append([]string{""}, f.emptyValues...)
		}
		missing := squirrel.Or{squirrel.Eq{col: nil}}
		present := squirrel.And{squirrel.NotEq{col: nil}}
		for _, e := range empties {
			missing = append(missing, squirrel.Eq{col: e})
			present = append(present, squirrel.NotEq{col: e})
		}
		if negate {
			return missing, nil
		}
		return present, nil
	default:
		return nil, fmt.Errorf("isMissing/isPresent operator is not supported for field: %s", field)
	}
}

func likeExpr(values map[string]any, pattern string, negate bool) (squirrel.Sqlizer, error) {
	if _, value, info, ok := singleField(values); ok {
		if info.IsTag || info.IsRole {
			return jsonExpr(info, squirrel.Like{"value": fmt.Sprintf(pattern, value)}, negate), nil
		}
		// LIKE can't use the column index, so annotation fields keep the COALESCE form: a NULL
		// column never matches LIKE, which would silently drop missing-annotation rows the original
		// COALESCE form included.
		if f, isAnnotation := annotationField(info); isAnnotation {
			return likeCond(f.coalesced(), fmt.Sprintf(pattern, value), negate), nil
		}
	}
	fields, err := sqlFields(values)
	if err != nil {
		return nil, err
	}
	if negate {
		lk := squirrel.NotLike{}
		for field, value := range fields {
			lk[field] = fmt.Sprintf(pattern, value)
		}
		return lk, nil
	}
	lk := squirrel.Like{}
	for field, value := range fields {
		lk[field] = fmt.Sprintf(pattern, value)
	}
	return lk, nil
}

func likeCond(col, pattern string, negate bool) squirrel.Sqlizer {
	if negate {
		return squirrel.NotLike{col: pattern}
	}
	return squirrel.Like{col: pattern}
}

func rangeExpr(values map[string]any) (squirrel.Sqlizer, error) {
	field, value, info, ok := singleField(values)
	if !ok {
		return nil, fmt.Errorf("invalid field in criteria: %s", field)
	}
	if info.IsTag || info.IsRole {
		// Tags/roles are multi-valued JSON, so splitting a range into two independent EXISTS
		// subqueries would let different values satisfy each bound. Ranges are unsupported there.
		return nil, fmt.Errorf("range operator not supported for tag/role field: %s", field)
	}
	s := reflect.ValueOf(value)
	if s.Kind() != reflect.Slice || s.Len() != 2 {
		return nil, fmt.Errorf("range criteria for %q must be a [min, max] pair, got: %v", field, value)
	}
	low, err := comparisonExpr(map[string]any{field: s.Index(0).Interface()}, cmpGe)
	if err != nil {
		return nil, err
	}
	high, err := comparisonExpr(map[string]any{field: s.Index(1).Interface()}, cmpLe)
	if err != nil {
		return nil, err
	}
	return squirrel.And{low, high}, nil
}

func periodExpr(values map[string]any, negate bool) (squirrel.Sqlizer, error) {
	fields, err := sqlFields(values)
	if err != nil {
		return nil, err
	}
	var field string
	var value any
	for f, v := range fields {
		field, value = f, v
		break
	}
	days, err := strconv.ParseInt(fmt.Sprintf("%v", value), 10, 64)
	if err != nil {
		return nil, err
	}
	firstDate := startOfPeriod(days, time.Now())
	if negate {
		return squirrel.Or{
			squirrel.Lt{field: firstDate},
			squirrel.Eq{field: nil},
		}, nil
	}
	return squirrel.Gt{field: firstDate}, nil
}

func startOfPeriod(numDays int64, from time.Time) string {
	return from.Add(time.Duration(-24*numDays) * time.Hour).Format("2006-01-02")
}

func (c smartPlaylistCriteria) inList(values map[string]any, negate bool) (squirrel.Sqlizer, error) {
	playlistID, ok := values["id"].(string)
	if !ok {
		return nil, errors.New("playlist id not given")
	}
	filters := squirrel.And{squirrel.Eq{"pl.playlist_id": playlistID}}
	if !c.owner.IsAdmin {
		if c.owner.ID == "" {
			filters = append(filters, squirrel.Eq{"playlist.public": 1})
		} else {
			filters = append(filters, squirrel.Or{
				squirrel.Eq{"playlist.public": 1},
				squirrel.Eq{"playlist.owner_id": c.owner.ID},
			})
		}
	}
	subQuery := squirrel.Select("media_file_id").
		From("playlist_tracks pl").
		LeftJoin("playlist on pl.playlist_id = playlist.id").
		Where(filters)
	subSQL, subArgs, err := subQuery.PlaceholderFormat(squirrel.Question).ToSql()
	if err != nil {
		return nil, err
	}
	if negate {
		return squirrel.Expr("media_file.id NOT IN ("+subSQL+")", subArgs...), nil
	}
	return squirrel.Expr("media_file.id IN ("+subSQL+")", subArgs...), nil
}

func jsonExpr(info criteria.FieldInfo, cond squirrel.Sqlizer, negate bool) squirrel.Sqlizer {
	if info.IsRole {
		return roleCond{role: info.Name(), cond: cond, not: negate}
	}
	return tagCond{tag: info.Name(), numeric: info.Numeric, cond: cond, not: negate}
}

type tagCond struct {
	tag     string
	numeric bool
	cond    squirrel.Sqlizer
	not     bool
}

func (e tagCond) ToSql() (string, []any, error) {
	var cond string
	var args []any
	var err error
	if e.cond != nil {
		cond, args, err = e.cond.ToSql()
		if e.numeric {
			cond = strings.ReplaceAll(cond, "value", "CAST(value AS REAL)")
		}
		cond = fmt.Sprintf("exists (select 1 from json_tree(media_file.tags, '$.%s') where key='value' and %s)", e.tag, cond)
	} else {
		cond = fmt.Sprintf("exists (select 1 from json_tree(media_file.tags, '$.%s') where key='value')", e.tag)
	}
	if e.not {
		cond = "not " + cond
	}
	return cond, args, err
}

type roleCond struct {
	role string
	cond squirrel.Sqlizer
	not  bool
}

func (e roleCond) ToSql() (string, []any, error) {
	var cond string
	var args []any
	if e.cond != nil {
		innerSQL, innerArgs, err := roleCondSQL(e.cond)
		if err != nil {
			return "", nil, err
		}
		cond = roleExistsSQL(innerSQL)
		args = append([]any{e.role}, innerArgs...)
	} else {
		cond = "exists (select 1 from media_file_artists mfa where mfa.media_file_id = media_file.id and mfa.role = ?)"
		args = []any{e.role}
	}
	if e.not {
		cond = "not " + cond
	}
	return cond, args, nil
}

// roleCondSQL extracts SQL from a squirrel condition and rewrites the placeholder column name.
func roleCondSQL(cond squirrel.Sqlizer) (string, []any, error) {
	sql, args, err := cond.ToSql()
	if err != nil {
		return "", nil, err
	}
	return strings.ReplaceAll(sql, "value", "artist.name"), args, nil
}

// roleExistsSQL wraps a condition fragment in the standard role EXISTS subquery.
func roleExistsSQL(innerCond string) string {
	return fmt.Sprintf("exists (select 1 from media_file_artists mfa join artist on artist.id = mfa.artist_id "+
		"where mfa.media_file_id = media_file.id and mfa.role = ? and %s)", innerCond)
}

// jsonCondBatchSize limits how many conditions are ORed inside a single EXISTS subquery
// to stay within SQLite's expression tree depth limit (max 1000). The EXISTS wrapper
// consumes ~4 levels; each ORed condition adds 1 level. Empirically, 496 is the maximum.
const jsonCondBatchSize = 350

// mergeJsonConds collapses multiple non-negated roleCond or tagCond entries for the same
// field within an OR group into batched EXISTS subqueries with the conditions ORed inside.
// This turns N separate correlated subqueries into ceil(N/batchSize), dramatically
// improving performance for smart playlists with many patterns.
func mergeJsonConds(or squirrel.Or) squirrel.Sqlizer {
	if merged, ok := mergeSameFieldConds(or, false); ok {
		return squirrel.Or(merged)
	}
	return or
}

// mergeNegatedJsonConds is the AND-group counterpart to mergeJsonConds, merging negated
// conditions. By De Morgan, "NOT EXISTS(X) AND NOT EXISTS(Y)" == "NOT EXISTS(X OR Y)".
func mergeNegatedJsonConds(and squirrel.And) squirrel.Sqlizer {
	if merged, ok := mergeSameFieldConds(and, true); ok {
		return squirrel.And(merged)
	}
	return and
}

// mergeSameFieldConds groups roleCond/tagCond entries that share a field and the requested
// polarity, replacing each group of 2+ with batched roleCondGroup/tagCondGroup subqueries.
// Returns the rewritten conditions and whether any merge happened.
func mergeSameFieldConds(conds []squirrel.Sqlizer, negated bool) ([]squirrel.Sqlizer, bool) {
	type condEntry struct {
		index int
		cond  squirrel.Sqlizer
	}
	type group struct {
		entries []condEntry
		isRole  bool
		numeric bool
		tag     string
	}
	groups := make(map[string]*group)
	for i, s := range conds {
		switch c := s.(type) {
		case roleCond:
			if c.not != negated || c.cond == nil {
				continue
			}
			g, exists := groups["role:"+c.role]
			if !exists {
				g = &group{isRole: true}
				groups["role:"+c.role] = g
			}
			g.entries = append(g.entries, condEntry{index: i, cond: c.cond})
		case tagCond:
			if c.not != negated || c.cond == nil {
				continue
			}
			g, exists := groups["tag:"+c.tag]
			if !exists {
				g = &group{tag: c.tag, numeric: c.numeric}
				groups["tag:"+c.tag] = g
			}
			g.entries = append(g.entries, condEntry{index: i, cond: c.cond})
		}
	}

	remove := make(map[int]bool)
	var additions []squirrel.Sqlizer
	for _, key := range slices.Sorted(maps.Keys(groups)) {
		g := groups[key]
		if len(g.entries) < 2 {
			continue
		}
		batchConds := make([]squirrel.Sqlizer, len(g.entries))
		for i, e := range g.entries {
			remove[e.index] = true
			batchConds[i] = e.cond
		}
		if g.isRole {
			role := key[len("role:"):]
			for batch := range slices.Chunk(batchConds, jsonCondBatchSize) {
				additions = append(additions, roleCondGroup{role: role, conds: batch, not: negated})
			}
		} else {
			for batch := range slices.Chunk(batchConds, jsonCondBatchSize) {
				additions = append(additions, tagCondGroup{tag: g.tag, numeric: g.numeric, conds: batch, not: negated})
			}
		}
	}

	if len(remove) == 0 {
		return conds, false
	}

	result := make([]squirrel.Sqlizer, 0, len(conds)-len(remove)+len(additions))
	for i, s := range conds {
		if !remove[i] {
			result = append(result, s)
		}
	}
	return append(result, additions...), true
}

// roleCondGroup represents multiple role conditions for the same role, merged into a single
// (optionally negated) EXISTS subquery for performance.
type roleCondGroup struct {
	role  string
	conds []squirrel.Sqlizer
	not   bool
}

func (g roleCondGroup) ToSql() (string, []any, error) {
	innerParts := make([]string, 0, len(g.conds))
	allArgs := []any{g.role}
	for _, c := range g.conds {
		part, args, err := roleCondSQL(c)
		if err != nil {
			return "", nil, err
		}
		innerParts = append(innerParts, part)
		allArgs = append(allArgs, args...)
	}
	cond := roleExistsSQL("(" + strings.Join(innerParts, " OR ") + ")")
	if g.not {
		cond = "not " + cond
	}
	return cond, allArgs, nil
}

// tagCondGroup represents multiple tag conditions for the same tag, merged into a single
// (optionally negated) EXISTS subquery for performance.
type tagCondGroup struct {
	tag     string
	numeric bool
	conds   []squirrel.Sqlizer
	not     bool
}

func (g tagCondGroup) ToSql() (string, []any, error) {
	innerParts := make([]string, 0, len(g.conds))
	var allArgs []any
	for _, c := range g.conds {
		part, args, err := c.ToSql()
		if err != nil {
			return "", nil, err
		}
		if g.numeric {
			part = strings.ReplaceAll(part, "value", "CAST(value AS REAL)")
		}
		innerParts = append(innerParts, part)
		allArgs = append(allArgs, args...)
	}
	cond := fmt.Sprintf("exists (select 1 from json_tree(media_file.tags, '$.%s') where key='value' and (%s))",
		g.tag, strings.Join(innerParts, " OR "))
	if g.not {
		cond = "not " + cond
	}
	return cond, allArgs, nil
}

func singleField(values map[string]any) (string, any, criteria.FieldInfo, bool) {
	if len(values) != 1 {
		return "", nil, criteria.FieldInfo{}, false
	}
	for field, value := range values {
		info, ok := criteria.LookupField(field)
		return field, value, info, ok
	}
	return "", nil, criteria.FieldInfo{}, false
}

func sqlFields(values map[string]any) (map[string]any, error) {
	fields := make(map[string]any, len(values))
	for field, value := range values {
		info, ok := criteria.LookupField(field)
		if !ok {
			return nil, fmt.Errorf("invalid field in criteria: %s", field)
		}
		if info.IsTag || info.IsRole {
			return nil, fmt.Errorf("tag and role criteria must contain exactly one field: %s", field)
		}
		sqlField, ok := fieldExpr(info.Name())
		if !ok || sqlField == "" {
			return nil, fmt.Errorf("invalid field in criteria: %s", field)
		}
		fields[sqlField] = value
	}
	return fields, nil
}

func fieldExpr(name string) (string, bool) {
	field, ok := smartPlaylistFields[strings.ToLower(name)]
	return field.expr, ok
}

// comparator is a scalar SQL comparison operator used by smart playlist criteria.
type comparator struct {
	build   func(map[string]any) squirrel.Sqlizer
	satisfy func(a, b float64) bool // the operator as a predicate, to reason about a column's COALESCE default
	// ordering is false only for = and <>, the only operators with a clean bare form over bool columns.
	ordering bool
}

var (
	cmpEq = comparator{build: func(f map[string]any) squirrel.Sqlizer { return squirrel.Eq(f) }, satisfy: func(a, b float64) bool { return a == b }}
	cmpNe = comparator{build: func(f map[string]any) squirrel.Sqlizer { return squirrel.NotEq(f) }, satisfy: func(a, b float64) bool { return a != b }}
	cmpGt = comparator{build: func(f map[string]any) squirrel.Sqlizer { return squirrel.Gt(f) }, satisfy: func(a, b float64) bool { return a > b }, ordering: true}
	cmpGe = comparator{build: func(f map[string]any) squirrel.Sqlizer { return squirrel.GtOrEq(f) }, satisfy: func(a, b float64) bool { return a >= b }, ordering: true}
	cmpLt = comparator{build: func(f map[string]any) squirrel.Sqlizer { return squirrel.Lt(f) }, satisfy: func(a, b float64) bool { return a < b }, ordering: true}
	cmpLe = comparator{build: func(f map[string]any) squirrel.Sqlizer { return squirrel.LtOrEq(f) }, satisfy: func(a, b float64) bool { return a <= b }, ordering: true}
)

// annotationField returns the field definition only for nullable annotation columns that have a
// COALESCE default (playcount, rating, loved). Date annotation columns (lastplayed, dateloved, ...)
// have no default and return ok=false.
func annotationField(info criteria.FieldInfo) (smartPlaylistField, bool) {
	f, ok := smartPlaylistFields[info.Name()]
	if !ok || f.coalesceDefault == nil {
		return smartPlaylistField{}, false
	}
	return f, true
}

// coalesced wraps the field in COALESCE(col, default) (bare expression if it has no default). Used
// where index-friendliness does not apply (ORDER BY, list comparisons) and the missing-row-as-default
// semantics must be kept.
func (f smartPlaylistField) coalesced() string {
	if f.coalesceDefault == nil {
		return f.expr
	}
	return fmt.Sprintf("COALESCE(%s, %s)", f.expr, sqlLiteral(f.coalesceDefault))
}

func comparisonExpr(values map[string]any, cmp comparator) (squirrel.Sqlizer, error) {
	if _, value, info, ok := singleField(values); ok {
		if info.IsTag || info.IsRole {
			return jsonExpr(info, cmp.build(map[string]any{"value": value}), false), nil
		}
		if f, isAnnotation := annotationField(info); isAnnotation {
			return annotationCond(f, cmp, value), nil
		}
	}
	fields, err := sqlFields(values)
	if err != nil {
		return nil, err
	}
	return cmp.build(fields), nil
}

// annotationCond builds a comparison against a nullable annotation column (see smartPlaylistField
// for why). When bareNullInclusion can reason about the comparison exactly it emits the
// index-friendly bare `col <cmp> ?`, adding `OR col IS NULL` only when the default would match;
// otherwise it falls back to the COALESCE form, which can't use the index but is always equivalent.
func annotationCond(f smartPlaylistField, cmp comparator, value any) squirrel.Sqlizer {
	wrapNull, ok := bareNullInclusion(f.coalesceDefault, cmp, value)
	if !ok {
		return cmp.build(map[string]any{f.coalesced(): value})
	}
	base := cmp.build(map[string]any{f.expr: value})
	if !wrapNull {
		return base
	}
	// The default satisfies the predicate, so missing rows (NULL) must be included. All comparators
	// (including <>) evaluate to false against NULL, so an explicit IS NULL restores them.
	return squirrel.Or{base, squirrel.Eq{f.expr: nil}}
}

// bareNullInclusion decides whether the index-friendly bare form is safe for this comparison and,
// if so, whether missing (NULL) rows must be re-included via `OR col IS NULL`. It returns ok=false
// when the bare form can't be proven equivalent to COALESCE(col, default) <cmp> ? — i.e. the value
// isn't a scalar the comparator can order exactly (lists, bool ordering, unparseable values) — in
// which case the caller keeps the COALESCE form. When ok=true, wrapNull is true iff the default
// value itself satisfies the predicate (so missing rows would match and must be preserved).
func bareNullInclusion(defaultVal any, cmp comparator, value any) (wrapNull, ok bool) {
	if b, isBool := defaultVal.(bool); isBool {
		// Bool columns have an exact bare form only for equality; ordering operators fall back to
		// COALESCE. Mapping both bools to 0/1 lets cmp.satisfy reuse the numeric eq/ne predicate.
		if cmp.ordering {
			return false, false
		}
		v, okV := criteria.ToBool(value)
		if !okV {
			return false, false
		}
		return cmp.satisfy(boolToFloat(b), boolToFloat(v)), true
	}
	d, okD := toFloat(defaultVal)
	v, okV := toFloat(value)
	if !okD || !okV {
		// Non-scalar or unparseable value: no exact bare form, keep COALESCE.
		return false, false
	}
	return cmp.satisfy(d, v), true
}

func boolToFloat(b bool) float64 {
	if b {
		return 1
	}
	return 0
}

// toFloat coerces a scalar criteria value to float64. Criteria values come from JSON (float64,
// string) or are built in Go (int, float64), so only those types are handled; anything else —
// including a slice or an unparseable string — reports ok=false so the caller keeps the COALESCE
// form instead of an index-friendly bare comparison.
func toFloat(v any) (float64, bool) {
	switch n := v.(type) {
	case float64:
		return n, true
	case int:
		return float64(n), true
	case int64:
		return float64(n), true
	case string:
		f, err := strconv.ParseFloat(n, 64)
		return f, err == nil
	default:
		return 0, false
	}
}

// sqlLiteral renders an annotation field's COALESCE default (0 or false) as a SQL literal for ORDER
// BY. %v renders both bool and numeric defaults correctly (false/true, 0).
func sqlLiteral(v any) string {
	return fmt.Sprintf("%v", v)
}

func fieldJoinType(name string) smartPlaylistJoinType {
	info, ok := criteria.LookupField(name)
	if !ok {
		return smartPlaylistJoinNone
	}
	field, ok := smartPlaylistFields[info.Name()]
	if !ok {
		return smartPlaylistJoinNone
	}
	return field.joinType
}

func (c smartPlaylistCriteria) ExpressionJoins() smartPlaylistJoinType {
	var joins smartPlaylistJoinType
	_ = criteria.Walk(c.Criteria.Expression, func(expr criteria.Expression) error {
		for field := range criteria.Fields(expr) {
			joins |= fieldJoinType(field)
		}
		return nil
	})
	return joins
}

func (c smartPlaylistCriteria) RequiredJoins() smartPlaylistJoinType {
	joins := c.ExpressionJoins()
	for _, name := range c.Criteria.SortFieldNames() {
		joins |= fieldJoinType(name)
	}
	return joins
}

func (c smartPlaylistCriteria) OrderBy() string {
	sortFields := c.Criteria.OrderByFields()
	parts := make([]string, 0, len(sortFields))
	for _, sf := range sortFields {
		mapped, ok := sortExpr(sf.Field)
		if !ok {
			continue
		}
		dir := "asc"
		if sf.Desc {
			dir = "desc"
		}
		parts = append(parts, mapped+" "+dir)
	}
	return strings.Join(parts, ", ")
}

func sortExpr(sortField string) (string, bool) {
	info, ok := criteria.LookupField(sortField)
	if !ok {
		return "", false
	}
	if field, ok := smartPlaylistFields[info.Name()]; ok && field.order != "" {
		return field.order, true
	}
	var mapped string
	switch {
	case info.IsTag:
		mapped = "COALESCE(json_extract(media_file.tags, '$." + info.Name() + "[0].value'), '')"
	case info.IsRole:
		mapped = "COALESCE(json_extract(media_file.participants, '$." + info.Name() + "[0].name'), '')"
	default:
		field, ok := smartPlaylistFields[info.Name()]
		if !ok || field.expr == "" {
			return "", false
		}
		// Sorting keeps the COALESCE default so missing-annotation rows sort as that default
		// (filtering drops COALESCE for index use, but ORDER BY has no index to preserve here).
		mapped = field.coalesced()
	}
	if info.Numeric {
		mapped = fmt.Sprintf("CAST(%s AS REAL)", mapped)
	}
	return mapped, true
}