-- M7 #353: album cover enrichment queries.

-- name: ListAlbumsMissingCover :many
-- M7 cover-sources: returns rows the enricher should attempt. Eligible:
-- cover_art_source IS NULL (never tried), OR cover_art_source = 'none'
-- AND cover_art_sources_version != current (settled under an older
-- source set; recheck against the current chain). LIMIT supplied by
-- caller for batching.
SELECT a.id
  FROM albums a
 WHERE a.cover_art_source IS NULL
    OR (a.cover_art_source = 'none' AND a.cover_art_sources_version != $1)
 ORDER BY a.created_at
 LIMIT $2;

-- name: ListAlbumsRetryMissing :many
-- Bulk-retry candidates: NULL (never tried) plus 'none' (tried, failed).
-- Used by the admin "refetch missing covers" endpoint. LIMIT supplied.
SELECT a.id, a.mbid, a.title, a.artist_id
  FROM albums a
 WHERE a.cover_art_source IS NULL OR a.cover_art_source = 'none'
 ORDER BY a.created_at ASC
 LIMIT $1;

-- name: GetAlbumWithFirstTrackPath :one
-- Returns the album row, the artist's name (used by name-based art
-- providers like Deezer/Last.fm), and one of its track file paths
-- (used to derive the album directory for sidecar writes). Pick
-- lowest position track for determinism.
SELECT a.id, a.mbid, a.title, a.cover_art_path, a.cover_art_source,
       ar.name AS artist_name,
       t.file_path AS track_file_path
  FROM albums a
  JOIN artists ar ON ar.id = a.artist_id
  LEFT JOIN tracks t ON t.album_id = a.id
 WHERE a.id = $1
 ORDER BY t.disc_number NULLS LAST, t.track_number NULLS LAST, t.id
 LIMIT 1;

-- name: SetAlbumCover :exec
-- Persist enrichment result. Pass cover_art_path = '' to clear (turns into NULL).
UPDATE albums
   SET cover_art_path   = NULLIF($2::text, ''),
       cover_art_source = $3
 WHERE id = $1;

-- name: ClearAlbumCover :exec
-- Used by the admin retry endpoint to wipe state before re-running the
-- enricher. Sets both fields to NULL.
UPDATE albums
   SET cover_art_path   = NULL,
       cover_art_source = NULL
 WHERE id = $1;

-- name: ClearAlbumCoverNone :exec
-- M7 #380: when MBID backfill heals an album, clear its 'none' cover_art_source
-- back to NULL so the enricher's next batch retries the MBCAA fetch.
-- Idempotent — only updates rows currently stamped 'none'.
UPDATE albums
   SET cover_art_source = NULL
 WHERE id = $1 AND cover_art_source = 'none';

-- name: CountAlbumCoverSources :one
-- Diagnostic for the admin overview: counts by source, including NULL.
-- Returns one row with named tallies.
SELECT
    COUNT(*) FILTER (WHERE cover_art_source IS NULL)     ::bigint AS untried,
    COUNT(*) FILTER (WHERE cover_art_source = 'sidecar') ::bigint AS sidecar,
    COUNT(*) FILTER (WHERE cover_art_source = 'embedded')::bigint AS embedded,
    COUNT(*) FILTER (WHERE cover_art_source = 'mbcaa')   ::bigint AS mbcaa,
    COUNT(*) FILTER (WHERE cover_art_source = 'none')    ::bigint AS none
  FROM albums;
