-- 0050_candidate_artist_tags.up.sql — folksonomy tags for OUT-OF-LIBRARY
-- artists (#2376, milestone #268 slice 5).
--
-- track_tags (0042) cannot hold these: it is FK'd to tracks(id), and a
-- Discover candidate has no local row by definition. So this is a parallel
-- cache keyed by the candidate's MusicBrainz MBID — the only stable identity
-- available for an artist we don't have.
--
-- Purpose is slice 6: rank suggestions by overlap between these tags and the
-- user's taste_profile_tags, turning "neighbour of an artist you play" into
-- "matches the sound you like".
--
-- GLOBAL, not per-user: a candidate's tags are a property of the artist, not
-- of anyone's taste. Nothing here is user-scoped, so rule #47 has nothing to
-- scope — the per-user part lives entirely in slice 6's ranking.
--
-- weight is a normalized folksonomy strength in [0,1], same scale as
-- track_tags, so the two can be compared without a conversion step.
CREATE TABLE candidate_artist_tags (
    candidate_mbid text             NOT NULL,
    tag            text             NOT NULL,
    weight         double precision NOT NULL DEFAULT 1,
    PRIMARY KEY (candidate_mbid, tag)
);

-- Enrichment bookkeeping. This is a SEPARATE table rather than columns on the
-- tags table, because the "providers had nothing" outcome must be recordable
-- for a candidate with zero tag rows — otherwise every empty candidate stays
-- eligible forever and the worker re-fetches it on every tick.
--
-- tracks solved the same problem with columns on `tracks` (0042), but there is
-- no per-candidate row anywhere to hang them off: artist_similarity_unmatched
-- is keyed (seed_artist_id, candidate_mbid, source) and holds MANY rows per
-- candidate.
--
-- Absence of a row here means "never processed", so unlike tracks.tag_source
-- this column can be NOT NULL — there is no null-means-pending state to model.
--   'musicbrainz' | 'lastfm' | 'mixed' → found, cached
--   'none'                             → providers confirmed nothing
--   tag_sources_version                → bump to re-process settled 'none'
--
-- A transient failure writes NO row at all (rather than a row it would then
-- have to distinguish), which leaves the candidate eligible for the next pass.
CREATE TABLE candidate_artist_tag_state (
    candidate_mbid      text        PRIMARY KEY,
    tag_source          text        NOT NULL,
    tag_sources_version integer     NOT NULL DEFAULT 0,
    updated_at          timestamptz NOT NULL DEFAULT now()
);

-- Serves the eligibility scan's "settled 'none' under an older version"
-- branch. The PK already covers the per-candidate lookups.
CREATE INDEX candidate_artist_tag_state_source_idx
    ON candidate_artist_tag_state (tag_source, tag_sources_version);
