-- +goose Up
-- +goose StatementBegin
-- Clean narrator/edition suffixes out of audiobook titles. The narrator
-- is already captured separately in item_people (kind=8) from the file's
-- narrator tag, so leaving it in the title field is duplicate data that
-- visibly clutters the UI ("Cytonic (UK Version: Read by Sophie Aldred)"
-- → "Cytonic"). The raw title is preserved in original_title for
-- forensic reference.
--
-- The regex must stay in lockstep with the Go scanner.stripNarratorSuffix
-- helper. Both run case-insensitive and target a trailing block of the
-- form: optional separator + optional "(UK Version:|US Version:|...|)"
-- + "read by X" + optional close-paren.

WITH cleaned AS (
    SELECT mi.content_id,
           mi.title AS raw_title,
           trim(regexp_replace(
               regexp_replace(
                   regexp_replace(
                       mi.title,
                       E'\\s*\\(?\\s*[-:,]?\\s*(UK Version:?|US Version:?)?\\s*[Rr]ead [Bb]y [A-Za-z0-9., ''&]+\\)?\\s*$',
                       '',
                       'g'
                   ),
                   E'\\s*\\(unabridged\\)\\s*', ' ', 'gi'
               ),
               E'\\s+', ' ', 'g'
           )) AS clean_title
    FROM media_items mi
    WHERE mi.type = 'audiobook'
      AND (mi.title ~* '\s+read by ' OR mi.title ~* '\(unabridged\)')
)
UPDATE media_items mi
SET
    title          = c.clean_title,
    sort_title     = LOWER(c.clean_title),
    original_title = CASE
                         WHEN COALESCE(mi.original_title, '') = '' THEN c.raw_title
                         ELSE mi.original_title
                     END,
    updated_at     = NOW()
FROM cleaned c
WHERE mi.content_id = c.content_id
  AND c.clean_title <> ''
  AND c.clean_title <> c.raw_title;
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
-- No-op rollback.
--
-- The up migration intentionally preserves scanner-supplied original_title.
-- Restoring title from original_title on rollback can overwrite newer title
-- edits for every audiobook, so the safe rollback is to leave data as-is.
-- +goose StatementEnd