-- Adds `user_stats.num_likes_received` — a MONOTONIC counter of the
-- "Like" reactions received across all of a user's media. Feeds the
-- `crowd-favorite` badge progression (metric = `reaction.like.received`).
--
-- Design alignment with `num_external_comments` / `wordsmith`:
--   - Lives on `user_stats` alongside the other tallies (no new table).
--   - Maintenance is a SQL trigger on `media_reaction` INSERT, filtered
--     to `value = 'like'` only — dislikes never count.
--   - NO trigger on UPDATE/DELETE: an unlike or a flip like→dislike
--     MUST NOT decrement (badges are "milestones reached", a withdrawn
--     like must not retroactively strip a tier). A flip
--     dislike→like is a SQL UPDATE on the existing row, so the
--     AFTER-INSERT trigger doesn't fire — which exactly matches the
--     product rule "do not reward changed-mind flips" (same rule the
--     service applies for the notification).
--   - Self-reactions are impossible by service-level guard
--     (MediaReactionService throws SelfReact), so no filter is needed.
--
-- Edge case documented: `like → unlike → like` by the same actor counts
-- +2 because each physical INSERT fires the trigger once. Symmetric to
-- the `wordsmith` rule "every posted comment counts, even soft-deleted".
-- A solo actor cannot farm tier-4 alone (the cap is per-(media,actor)
-- row, but the counter is per-event); the abuse surface is acceptable.
--
-- Backfill sums every existing `like` row grouped by the owning user.

ALTER TABLE `user_stats`
    ADD COLUMN `num_likes_received` BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER `num_external_comments`;

DELIMITER //

DROP TRIGGER IF EXISTS `trg_media_reaction_ai_likes_received` //

CREATE TRIGGER `trg_media_reaction_ai_likes_received`
AFTER INSERT ON `media_reaction`
FOR EACH ROW
BEGIN
    DECLARE v_owner_id BINARY(16);

    -- Only Like reactions feed the counter. The `value` column stores
    -- the same string the PHP enum emits ('like' / 'dislike') — see
    -- MediaReactionValue.
    IF NEW.`value` = 'like' THEN
        SELECT `user_id`
          INTO v_owner_id
          FROM `media`
         WHERE `id` = NEW.`media_id`
         LIMIT 1;

        IF v_owner_id IS NOT NULL THEN
            INSERT INTO `user_stats` (`user_id`, `num_likes_received`)
            VALUES (v_owner_id, 1)
            ON DUPLICATE KEY UPDATE
                `num_likes_received` = `num_likes_received` + 1;
        END IF;
    END IF;
END //

DELIMITER ;

-- Backfill historical state — counts every existing `like` row regardless
-- of timestamp. Self-likes have always been blocked at the service layer
-- so the GROUP BY is safe without an extra join filter.
UPDATE `user_stats` us
   SET us.`num_likes_received` = (
        SELECT COUNT(*)
          FROM `media_reaction` mr
          JOIN `media` m ON m.`id` = mr.`media_id`
         WHERE mr.`value` = 'like'
           AND m.`user_id` = us.`user_id`
   );
