-- @database: main
--
-- Makes `user_bookmark` cascade when the media or the account it points at is
-- deleted.
--
-- Symptom: deleting your own media returned a 500 as soon as ANYONE had
-- bookmarked it —
--   SQLSTATE[23000] 1451 Cannot delete or update a parent row:
--   a foreign key constraint fails (`user_bookmark`, CONSTRAINT `user_bookmark_media`)
-- A stranger's private bookmark could therefore make an owner's media
-- undeletable, which is both a broken feature and a data-protection problem:
-- the owner has a right to erase their content.
--
-- Root cause: `media` has 18 child tables and 17 of them cascade;
-- `user_bookmark` was the sole exception. `MediaDeleteService` deletes the
-- `hxa_bo` side tables by hand (different database, no FK possible) and relies
-- on the cascade for everything in `hxa` — its own comment says so. So this is a
-- schema oversight in the bookmarks feature, not a deliberate restriction.
--
-- `user_bookmark_user` gets the same treatment. It was NOT the reported failure
-- and was the only FK referencing `user` in the whole schema without a cascade —
-- a trap primed for whoever first hard-deletes an account. Fixing one half of a
-- table and leaving the other would just move the surprise.
--
-- `fk_ub_collection` is deliberately left as SET NULL: deleting a collection
-- must NOT destroy the bookmarks in it, they fall back to the unclassified
-- bucket. That one was right.
--
-- Bookmarks are per-user private data; a bookmark whose media or owner is gone
-- has no meaning to anybody, so cascading destroys nothing worth keeping.

ALTER TABLE `user_bookmark`
    DROP FOREIGN KEY `user_bookmark_media`;

ALTER TABLE `user_bookmark`
    ADD CONSTRAINT `user_bookmark_media`
        FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE;

ALTER TABLE `user_bookmark`
    DROP FOREIGN KEY `user_bookmark_user`;

ALTER TABLE `user_bookmark`
    ADD CONSTRAINT `user_bookmark_user`
        FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE;
