-- name: ListPlayedTracksNeedingSimilarity :many
-- Tracks with at least one play, an MBID, and no fresh listenbrainz row
-- (no row at all, OR fetched_at older than 7 days). Used by the worker
-- to find work each tick. Bounded by $1.
SELECT DISTINCT t.id, t.mbid
FROM tracks t
JOIN play_events pe ON pe.track_id = t.id
WHERE t.mbid IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM track_similarity ts
      WHERE ts.track_a_id = t.id
        AND ts.source = 'listenbrainz'
        AND ts.fetched_at > now() - interval '7 days'
  )
ORDER BY t.id
LIMIT $1;

-- name: ListPlayedArtistsNeedingSimilarity :many
SELECT DISTINCT ar.id, ar.mbid
FROM artists ar
JOIN tracks t ON t.artist_id = ar.id
JOIN play_events pe ON pe.track_id = t.id
WHERE ar.mbid IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM artist_similarity asim
      WHERE asim.artist_a_id = ar.id
        AND asim.source = 'listenbrainz'
        AND asim.fetched_at > now() - interval '7 days'
  )
ORDER BY ar.id
LIMIT $1;

-- name: GetTracksByMBIDs :many
-- Bulk in-library lookup: maps a slice of MBIDs back to local track IDs.
SELECT id, mbid FROM tracks WHERE mbid = ANY($1::text[]);

-- name: GetArtistsByMBIDs :many
SELECT id, mbid FROM artists WHERE mbid = ANY($1::text[]);

-- name: UpsertTrackSimilarity :exec
INSERT INTO track_similarity (track_a_id, track_b_id, score, source, fetched_at)
VALUES ($1, $2, $3, 'listenbrainz', now())
ON CONFLICT (track_a_id, track_b_id, source)
DO UPDATE SET score = EXCLUDED.score, fetched_at = EXCLUDED.fetched_at;

-- name: UpsertArtistSimilarity :exec
INSERT INTO artist_similarity (artist_a_id, artist_b_id, score, source, fetched_at)
VALUES ($1, $2, $3, 'listenbrainz', now())
ON CONFLICT (artist_a_id, artist_b_id, source)
DO UPDATE SET score = EXCLUDED.score, fetched_at = EXCLUDED.fetched_at;

-- name: UpsertArtistSimilarityUnmatched :exec
-- Persists an out-of-library similar-artist MBID. Idempotent on
-- (seed_artist_id, candidate_mbid, source) — re-fetches refresh the
-- name/score and bump fetched_at.
INSERT INTO artist_similarity_unmatched (
    seed_artist_id, candidate_mbid, candidate_name, score, source
) VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (seed_artist_id, candidate_mbid, source) DO UPDATE SET
    candidate_name = EXCLUDED.candidate_name,
    score          = EXCLUDED.score,
    fetched_at     = now();

-- name: ListSimilarArtistsForArtist :many
-- In-library artists similar to a seed artist, ranked by best similarity
-- score across sources (deduped per candidate). cover_album_id + album_count
-- mirror ListArtistsAlphaWithCovers so the strip renders identical cards.
SELECT sqlc.embed(artists),
       cov.id AS cover_album_id,
       cnt.album_count::bigint AS album_count
FROM (
    SELECT artist_b_id, max(score) AS sim_score
    FROM artist_similarity
    WHERE artist_a_id = sqlc.arg(seed_artist_id)
    GROUP BY artist_b_id
) s
JOIN artists ON artists.id = s.artist_b_id
LEFT JOIN LATERAL (
    SELECT id FROM albums
    WHERE artist_id = artists.id AND cover_art_path IS NOT NULL
    ORDER BY created_at DESC LIMIT 1
) cov ON true
LEFT JOIN LATERAL (
    SELECT count(*) AS album_count
    FROM albums WHERE artist_id = artists.id
) cnt ON true
ORDER BY s.sim_score DESC, artists.sort_name
LIMIT sqlc.arg(result_limit);