-- Track-tag enrichment queries (milestone #160, #1490). The tag enricher
-- drains ListTracksMissingTags, fetches from the provider chain, and
-- writes the merged top-K via DeleteTrackTags + InsertTrackTag, then
-- stamps SetTrackTagSource. Mirrors the coverart artist-enricher shape.

-- name: ListTracksMissingTags :many
-- Tracks eligible for tag enrichment: never processed (tag_source NULL)
-- or previously settled 'none' under an older provider version. Returns
-- the fields the provider chain needs — recording MBID (nullable) for
-- keyed lookups, title + artist name for name-based fallback, and the
-- artist MBID (nullable) for MusicBrainz's artist-tag fallback (#1519).
-- $1 = current tag_sources_version, $2 = limit.
SELECT t.id, t.mbid, t.title, a.name AS artist_name, a.mbid AS artist_mbid
  FROM tracks t
  JOIN artists a ON a.id = t.artist_id
 WHERE t.tag_source IS NULL
    OR (t.tag_source = 'none' AND t.tag_sources_version < $1)
 ORDER BY t.id
 LIMIT $2;

-- name: DeleteTrackTags :exec
-- Clear a track's cached tags before rewriting (atomic replace by the caller).
DELETE FROM track_tags WHERE track_id = $1;

-- name: InsertTrackTag :exec
-- Upsert one (track, tag); keep the stronger weight when two providers
-- agree on a tag with different folksonomy strengths.
INSERT INTO track_tags (track_id, tag, weight)
VALUES ($1, $2, $3)
ON CONFLICT (track_id, tag)
    DO UPDATE SET weight = GREATEST(track_tags.weight, EXCLUDED.weight);

-- name: SetTrackTagSource :exec
-- Stamp the enrichment outcome so the batch drainer skips settled rows.
-- $2 = 'lastfm' | 'musicbrainz' | 'mixed' | 'none', $3 = current version.
UPDATE tracks SET tag_source = $2, tag_sources_version = $3 WHERE id = $1;

-- name: ListPlayedTrackTagsForUser :many
-- The enriched tags for every track this user played inside the taste
-- window, so the recompute can union them into the tag facet alongside
-- ID3 genre. $1 = user_id, $2 = window_days. One row per (track, tag).
SELECT tt.track_id, tt.tag, tt.weight
  FROM track_tags tt
 WHERE tt.track_id IN (
       SELECT DISTINCT pe.track_id
         FROM play_events pe
        WHERE pe.user_id = $1
          AND pe.was_skipped = false
          AND pe.started_at > now() - make_interval(days => $2::int)
   );

-- name: ListLikedTrackTagsForUser :many
-- Enriched tags for the user's liked tracks — the like-bonus path in the
-- recompute mirrors the play path, so it needs the same tag lookup.
SELECT tt.track_id, tt.tag, tt.weight
  FROM track_tags tt
  JOIN general_likes gl ON gl.track_id = tt.track_id
 WHERE gl.user_id = $1;

-- name: ListTrackTagsForTracks :many
-- Enriched tags for a set of candidate tracks (#1534), so the scorer can
-- derive each candidate's mood buckets at scoring time. One row per (track,
-- tag); the caller filters to mood words via the internal/mood vocabulary.
SELECT tt.track_id, tt.tag
  FROM track_tags tt
 WHERE tt.track_id = ANY($1::uuid[]);
