-- 0081_wanted.up.sql — the wanted list (Scribe milestone #502, #5520).
--
-- With Lidarr gone, Minstrel keeps its own record of what it is looking for:
--
--   release_type_profiles  which kinds of release a follow wants: primary
--                          types (Album, EP, Single…) and the secondary types
--                          allowed (Live, Compilation, Remix…). An empty
--                          secondary list means studio releases only, which
--                          is how live recordings are left out.
--   follows                artists whose new releases are wanted, each on a
--                          profile.
--   follow_seen_groups     release groups a follow has already judged, so
--                          each is judged once.
--   wants                  release groups wanted: a request, a followed
--                          artist's new release, or an album whose files went
--                          missing. A want is held once the library has a
--                          full copy (internal/wanted).
--
-- lidarr_requests becomes requests with no Lidarr columns: approving a request
-- now records a want or a follow instead of sending an add to Lidarr. The
-- missing-file sweeper's attempt bookkeeping goes too; retrying belongs to the
-- search schedule (#5521), which works per want.

CREATE TABLE release_type_profiles (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name            text NOT NULL UNIQUE,
    primary_types   text[] NOT NULL,
    secondary_types text[] NOT NULL DEFAULT '{}',
    is_default      boolean NOT NULL DEFAULT false,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT release_type_profiles_primary_nonempty CHECK (cardinality(primary_types) > 0)
);

-- At most one default; the service keeps exactly one by moving it.
CREATE UNIQUE INDEX release_type_profiles_one_default
    ON release_type_profiles (is_default) WHERE is_default;

-- Lidarr's "Standard" metadata profile, which every followed artist used
-- (note #5527): albums, EPs and singles, studio releases only.
INSERT INTO release_type_profiles (name, primary_types, secondary_types, is_default)
VALUES ('Standard', ARRAY['Album', 'EP', 'Single'], '{}', true);

CREATE TABLE follows (
    artist_mbid text PRIMARY KEY,
    artist_name text NOT NULL,
    -- RESTRICT: a profile in use cannot be deleted out from under a follow.
    profile_id  uuid NOT NULL REFERENCES release_type_profiles (id) ON DELETE RESTRICT,
    followed_by uuid REFERENCES users (id) ON DELETE SET NULL,
    followed_at timestamptz NOT NULL DEFAULT now(),
    -- When the first discography read marked everything then listed as seen.
    -- NULL until then: a follow wants only what appears after it.
    baseline_at timestamptz,
    -- Whether the baseline also wants what the artist had already released
    -- that the profile passes and the library lacks: an approved artist
    -- request, or the operator ticking "also want…". Cleared once applied.
    want_existing boolean NOT NULL DEFAULT false,
    -- The discography read this follow last applied.
    checked_at  timestamptz,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX follows_profile_idx ON follows (profile_id);

CREATE TABLE follow_seen_groups (
    artist_mbid        text NOT NULL REFERENCES follows (artist_mbid) ON DELETE CASCADE,
    release_group_mbid text NOT NULL,
    seen_at            timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (artist_mbid, release_group_mbid)
);

CREATE TABLE wants (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    release_group_mbid text NOT NULL UNIQUE,
    artist_mbid        text NOT NULL,
    artist_name        text NOT NULL,
    title              text NOT NULL,
    primary_type       text NOT NULL DEFAULT '',
    first_release_date text NOT NULL DEFAULT '',
    reason             text NOT NULL
        CONSTRAINT wants_reason_check CHECK (reason IN ('request', 'follow', 'missing')),
    -- The library album this want is about (a missing want), or the one that
    -- answered it.
    album_id           uuid REFERENCES albums (id) ON DELETE SET NULL,
    created_by         uuid REFERENCES users (id) ON DELETE SET NULL,
    -- When it was last wanted: set again when a held want is reopened, so
    -- "arrived since it was wanted" measures from the reopening.
    wanted_at          timestamptz NOT NULL DEFAULT now(),
    fulfilled_at       timestamptz,
    created_at         timestamptz NOT NULL DEFAULT now(),
    updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX wants_open_idx ON wants (wanted_at) WHERE fulfilled_at IS NULL;
CREATE INDEX wants_artist_idx ON wants (artist_mbid);

-- Requests lose Lidarr.
DROP TABLE missing_reacquisitions;

ALTER TABLE lidarr_requests RENAME TO requests;
ALTER TYPE lidarr_request_status RENAME TO request_status;
ALTER TYPE lidarr_request_kind RENAME TO request_kind;
ALTER TABLE requests RENAME COLUMN lidarr_artist_mbid TO artist_mbid;
ALTER TABLE requests RENAME COLUMN lidarr_album_mbid TO album_mbid;
ALTER TABLE requests RENAME COLUMN lidarr_track_mbid TO track_mbid;
ALTER TABLE requests DROP COLUMN quality_profile_id;
ALTER TABLE requests DROP COLUMN root_folder_path;
ALTER TABLE requests DROP COLUMN lidarr_add_confirmed_at;
ALTER INDEX lidarr_requests_user_id_idx RENAME TO requests_user_id_idx;
ALTER INDEX lidarr_requests_status_idx RENAME TO requests_status_idx;
ALTER INDEX lidarr_requests_artist_mbid_idx RENAME TO requests_artist_mbid_idx;
ALTER INDEX lidarr_requests_album_mbid_idx RENAME TO requests_album_mbid_idx;

-- Requests made before #5241 name albums by release id. A want is keyed by
-- release group, so repoint every request the library can translate.
UPDATE requests r
   SET album_mbid = a.release_group_mbid,
       updated_at = now()
  FROM albums a
 WHERE a.mbid = r.album_mbid
   AND a.release_group_mbid IS NOT NULL
   AND a.release_group_mbid <> r.album_mbid;

-- Approved album and track requests still waiting become wants, judged by
-- the same test as any other (internal/wanted).
INSERT INTO wants (release_group_mbid, artist_mbid, artist_name, title, reason, created_by, wanted_at)
SELECT DISTINCT ON (r.album_mbid)
       r.album_mbid, r.artist_mbid, r.artist_name, COALESCE(r.album_title, ''), 'request', r.user_id, r.requested_at
  FROM requests r
 WHERE r.status = 'approved'
   AND r.kind IN ('album', 'track')
   AND r.album_mbid IS NOT NULL
 ORDER BY r.album_mbid, r.requested_at
ON CONFLICT (release_group_mbid) DO NOTHING;

-- The library's album artists start out followed on the default profile
-- (operator, 2026-10-09: "we already have artists in minstrel those start as
-- the wanted artists"). Various Artists and MusicBrainz's bracketed
-- placeholders ([unknown], [anonymous]…) are not artists anyone follows.
INSERT INTO follows (artist_mbid, artist_name, profile_id)
SELECT ar.mbid, ar.name, p.id
  FROM artists ar
 CROSS JOIN (SELECT id FROM release_type_profiles WHERE is_default) p
 WHERE ar.mbid IS NOT NULL AND ar.mbid <> ''
   AND ar.mbid <> '89ad4ac3-39f7-470e-963a-56509c546377'
   AND ar.name NOT LIKE '[%]'
   AND EXISTS (SELECT 1 FROM albums al WHERE al.artist_id = ar.id)
ON CONFLICT (artist_mbid) DO NOTHING;

-- An approved artist request was an artist added to Lidarr: it is followed.
INSERT INTO follows (artist_mbid, artist_name, profile_id, followed_by)
SELECT DISTINCT ON (r.artist_mbid) r.artist_mbid, r.artist_name, p.id, r.decided_by
  FROM requests r
 CROSS JOIN (SELECT id FROM release_type_profiles WHERE is_default) p
 WHERE r.status = 'approved' AND r.kind = 'artist'
 ORDER BY r.artist_mbid, r.requested_at
ON CONFLICT (artist_mbid) DO NOTHING;

-- The sweeper now only records a want; how often to look again is the
-- search schedule's business.
ALTER TABLE reacquisition_settings
    DROP COLUMN backoff_base_hours,
    DROP COLUMN backoff_max_hours,
    DROP COLUMN max_attempts,
    DROP COLUMN auto_approve;