-- @database: main
--
-- User blocking — one account cutting off all interaction with another.
--
-- The row is DIRECTIONAL (who blocked whom, kept for the "my blocks" listing
-- and for unblocking), but its EFFECTS are symmetric: once a pair appears here
-- in either direction, neither side can follow, react to or comment on the
-- other, and neither shows up in the other's MySQL-backed listings. The
-- application therefore always tests the pair BOTH WAYS
-- ({@see \Hydrogen\Domain\User\Block\UserBlockService::isBlockedBetween()}) —
-- a blocked user must not regain reach simply by not having blocked back.
--
-- Two indexes on purpose:
--   - the PRIMARY KEY answers "whom do I block?" (the listing, and the
--     forward lookup);
--   - `idx_ub_reverse` answers "who blocks me?" — needed on every read path,
--     since the effects apply to the blocked side too and that side has no
--     row of its own to look up.
--
-- Deliberately NOT enforced here: reporting. Blocking must never prevent
-- someone from reporting the account that blocked them.

CREATE TABLE `user_block` (
    `blocker_id` BINARY(16) NOT NULL,               -- who pressed "block"
    `blocked_id` BINARY(16) NOT NULL,               -- who is cut off
    `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`blocker_id`, `blocked_id`),
    KEY `idx_ub_reverse` (`blocked_id`, `blocker_id`),
    -- Blocking yourself is meaningless and would poison every symmetric test.
    CONSTRAINT `chk_ub_not_self` CHECK (`blocker_id` <> `blocked_id`),
    CONSTRAINT `fk_ub_blocker` FOREIGN KEY (`blocker_id`)
        REFERENCES `user`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ub_blocked` FOREIGN KEY (`blocked_id`)
        REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
