-- Normalize per-page tags. Originally (0023) `page_tags.tag` stored the
-- tag inline as free-form text, the lone un-normalized tag concept while
-- authors/genres/manga-tags all went through a lookup table + FK. This
-- migration folds page tags into the SAME shared `tags` table that
-- `manga_tags` uses (0009): `page_tags.tag` becomes `page_tags.tag_id`
-- referencing `tags(id)`. After this there is one global tag vocabulary
-- shared by manga tags and page tags.
--
-- The HTTP contract is unchanged — the API still speaks tag *names*; the
-- repo resolves name<->id via `repo::tag::upsert_by_name`, exactly as the
-- manga-tag path does.

-- 1. New FK column, nullable until backfilled.
ALTER TABLE page_tags
    ADD COLUMN tag_id uuid REFERENCES tags(id) ON DELETE CASCADE;

-- 2. Seed the lookup table from the existing inline values, deduping
--    case-insensitively via the tags (lower(name)) unique index. Mirrors
--    the author backfill in 0009.
INSERT INTO tags (name)
    SELECT DISTINCT tag FROM page_tags
ON CONFLICT (lower(name)) DO NOTHING;

-- 3. Point every page_tags row at its canonical tag row.
UPDATE page_tags pt
   SET tag_id = t.id
  FROM tags t
 WHERE lower(t.name) = lower(pt.tag);

-- 4. The FK is now mandatory.
ALTER TABLE page_tags ALTER COLUMN tag_id SET NOT NULL;

-- 5. Swap the inline-tag indexes/constraints for tag_id equivalents.
DROP INDEX IF EXISTS page_tags_user_page_tag_uniq;
CREATE UNIQUE INDEX page_tags_user_page_tag_uniq
    ON page_tags (user_id, page_id, tag_id);

DROP INDEX IF EXISTS page_tags_user_tag_idx;
CREATE INDEX page_tags_user_tag_idx
    ON page_tags (user_id, tag_id, created_at DESC);

-- page_tags_user_idx (user_id, created_at DESC) and page_tags_page_idx
-- (page_id) are unaffected and stay as-is.

-- 6. Drop the now-dead inline column. This also drops the
--    page_tags_tag_nonempty CHECK; the 1..=64 length bound is now
--    enforced by `repo::tag::upsert_by_name` before a row is created.
ALTER TABLE page_tags DROP COLUMN tag;
