-- Precomputed author sort key for `?sort=author`.
--
-- The author sort used a correlated `min(lower(a.name))` subquery as the ORDER
-- BY key, evaluated per filter-matching row before LIMIT — it scaled worse than
-- the indexed date/title sorts. Materialize the same value on `mangas` so the
-- sort is a plain indexed column read.
--
-- `sort_author` = the alphabetically-first attached author's lowercased name,
-- or NULL when the manga has no authors (kept last via NULLS LAST in the query).
-- Author names are immutable (authors are upserted by unique lowercased name and
-- never renamed), so the value only changes when the manga_authors join changes
-- — maintained by the triggers below.

ALTER TABLE mangas ADD COLUMN sort_author text;

CREATE INDEX mangas_sort_author_idx ON mangas (sort_author, id);

-- Backfill from existing links.
UPDATE mangas m
   SET sort_author = (
        SELECT min(lower(a.name))
          FROM manga_authors ma
          JOIN authors a ON a.id = ma.author_id
         WHERE ma.manga_id = m.id
   );

CREATE OR REPLACE FUNCTION refresh_manga_sort_author(mid uuid) RETURNS void AS $$
BEGIN
    UPDATE mangas
       SET sort_author = (
            SELECT min(lower(a.name))
              FROM manga_authors ma
              JOIN authors a ON a.id = ma.author_id
             WHERE ma.manga_id = mid
       )
     WHERE id = mid;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION mangas_sort_author_on_ma_change() RETURNS trigger AS $$
BEGIN
    -- On a manga cascade-delete the UPDATE simply no-ops (row already gone).
    PERFORM refresh_manga_sort_author(COALESCE(NEW.manga_id, OLD.manga_id));
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER manga_authors_sort_author_ins AFTER INSERT ON manga_authors
    FOR EACH ROW EXECUTE FUNCTION mangas_sort_author_on_ma_change();
CREATE TRIGGER manga_authors_sort_author_del AFTER DELETE ON manga_authors
    FOR EACH ROW EXECUTE FUNCTION mangas_sort_author_on_ma_change();
