-- Folksonomy tags for out-of-library Discover candidates (#2376). Parallel to
-- track_tags.sql, but keyed by MBID because the artist has no local row. See
-- 0050_candidate_artist_tags.up.sql for why the bookkeeping is its own table.

-- name: ListCandidateArtistsMissingTags :many
-- Candidates eligible for tag enrichment: never processed (no state row) or
-- settled 'none' under an older provider version.
--
-- artist_similarity_unmatched holds one row per (seed, candidate, source), so
-- this GROUPs to one row per candidate — enriching the same MBID once per seed
-- that pointed at it would multiply the API calls for no gain.
--
-- ORDER BY summed similarity DESC is the load-bearing part. The candidate pool
-- is O(library artists x neighbours per artist) — thousands — and MusicBrainz
-- allows ~1 req/s, so it can NEVER be fully enriched in one pass. Draining in
-- strength order means the candidates most likely to actually reach a user's
-- deck get tags first, and the long tail fills in over subsequent ticks
-- instead of starving behind it.
--
-- Already-in-library candidates are skipped: they have an artists row, so
-- their tags belong in track_tags, and the suggestion query filters them out
-- anyway. $1 = current tag_sources_version, $2 = limit.
-- candidate_name is coalesced to the empty string so it lands non-nullable in
-- Go: the name is only a Last.fm lookup key, and empty simply means "MBID-keyed
-- providers only", which the provider chain already handles. max() is an
-- arbitrary-but-deterministic pick when several seeds spell one MBID
-- differently.
SELECT u.candidate_mbid,
       coalesce(max(u.candidate_name), '')::text AS candidate_name,
       sum(u.score)::float8                      AS total_score
  FROM artist_similarity_unmatched u
  LEFT JOIN candidate_artist_tag_state s ON s.candidate_mbid = u.candidate_mbid
 WHERE NOT EXISTS (SELECT 1 FROM artists a WHERE a.mbid = u.candidate_mbid)
   AND (
       s.candidate_mbid IS NULL
       OR (s.tag_source = 'none' AND s.tag_sources_version < $1)
   )
 GROUP BY u.candidate_mbid
 ORDER BY total_score DESC, u.candidate_mbid
 LIMIT $2;

-- name: DeleteCandidateArtistTags :exec
-- Clear a candidate's cached tags before rewriting (atomic replace by the
-- caller, same shape as DeleteTrackTags).
DELETE FROM candidate_artist_tags WHERE candidate_mbid = $1;

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

-- name: SetCandidateArtistTagState :exec
-- Stamp the enrichment outcome so the drainer skips settled candidates.
-- $2 = 'musicbrainz' | 'lastfm' | 'mixed' | 'none', $3 = current version.
INSERT INTO candidate_artist_tag_state (candidate_mbid, tag_source, tag_sources_version)
VALUES ($1, $2, $3)
ON CONFLICT (candidate_mbid) DO UPDATE
   SET tag_source          = EXCLUDED.tag_source,
       tag_sources_version = EXCLUDED.tag_sources_version,
       updated_at          = now();

-- name: ListCandidateArtistTagsForMbids :many
-- Cached tags for a set of candidates, for slice 6's taste-overlap ranking.
-- One row per (candidate, tag).
SELECT candidate_mbid, tag, weight
  FROM candidate_artist_tags
 WHERE candidate_mbid = ANY($1::text[]);

-- name: CountCandidateArtistTagCoverage :one
-- Operator-facing coverage: how many distinct candidates have been processed,
-- and how many of those actually yielded tags. The gap is the honest ceiling
-- from the task — obscure artists with no MBID presence or no upstream tags
-- stay thin no matter how long the worker runs, and that is worth being able
-- to see rather than inferring from a silent surface.
SELECT count(*)::bigint AS processed,
       count(*) FILTER (WHERE tag_source <> 'none')::bigint AS with_tags
  FROM candidate_artist_tag_state;

-- name: GcDeleteOrphanedCandidateArtistTags :execrows
-- Drops cached tags for candidates that no longer appear in the similarity
-- feed, or that have since been added to the library (their tags now live in
-- track_tags). The feed is refetched periodically and churns, so without this
-- the cache only ever grows.
DELETE FROM candidate_artist_tags t
 WHERE NOT EXISTS (
       SELECT 1 FROM artist_similarity_unmatched u
        WHERE u.candidate_mbid = t.candidate_mbid
   )
    OR EXISTS (SELECT 1 FROM artists a WHERE a.mbid = t.candidate_mbid);

-- name: GcDeleteOrphanedCandidateArtistTagState :execrows
-- Same sweep for the bookkeeping rows. Kept as a separate statement rather
-- than a cascade: the two tables are independent by design (a 'none' outcome
-- has state but no tags), so neither can be the parent of the other.
DELETE FROM candidate_artist_tag_state s
 WHERE NOT EXISTS (
       SELECT 1 FROM artist_similarity_unmatched u
        WHERE u.candidate_mbid = s.candidate_mbid
   )
    OR EXISTS (SELECT 1 FROM artists a WHERE a.mbid = s.candidate_mbid);
