-- Aggregated like notifications.
--
-- Likes are high-volume, so instead of one notification row per like we keep a
-- single row per (recipient, post) and aggregate: actor_id holds the most
-- recent liker, actor_count how many distinct likers have aggregated into it.
-- The partial unique index lets the like path UPSERT that one row (bumping the
-- count, refreshing the actor, re-marking unread) — so the activity list shows
-- "X and N others liked your post", not a flood.
ALTER TABLE notifications ADD COLUMN IF NOT EXISTS post_id UUID REFERENCES posts(id) ON DELETE CASCADE;
ALTER TABLE notifications ADD COLUMN IF NOT EXISTS actor_count INTEGER NOT NULL DEFAULT 1;

CREATE UNIQUE INDEX IF NOT EXISTS notifications_post_like_uq
    ON notifications (user_id, post_id)
    WHERE kind = 'post_like';