-- Adds `user_stats.num_external_comments` — a MONOTONIC counter of
-- comments the user posted on OTHER users' media (self-comments are
-- excluded). Feeds the `wordsmith` badge progression
-- (metric = `comment.posted`).
--
-- Design alignment with `num_media` / `shutterbug`:
--   - The counter lives on `user_stats` alongside the other tallies, so
--     no new table is introduced and Hydrogen never UPDATEs it directly
--     (UserStatsRepository contract).
--   - Maintenance is done by a SQL trigger on `media_comment` INSERT —
--     identical pattern to how `num_media` is fed by `media` INSERT.
--   - NO trigger on UPDATE/DELETE: soft-delete is a `deleted_at` UPDATE
--     (must NOT decrement — badges are "milestones reached", a moderation
--     soft-delete must not retroactively strip a tier). Hard-delete via
--     media CASCADE physically removes the row but the counter stays
--     where it is — past contribution is past.
--   - Self-comments are excluded inside the trigger via a join on
--     `media.user_id` (single PK lookup, negligible cost on insert).
--
-- Backfill cumulates EVERY historical comment that meets the rule, even
-- those soft-deleted, so live data matches the trigger's go-forward
-- semantics (monotonic, self-excluded).

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

DELIMITER //

DROP TRIGGER IF EXISTS `trg_media_comment_ai_external_count` //

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

    SELECT `user_id`
      INTO v_owner_id
      FROM `media`
     WHERE `id` = NEW.`media_id`
     LIMIT 1;

    -- Self-comments (`actor == media owner`) MUST NOT count toward the
    -- `wordsmith` progression — same call-site rule documented in the
    -- product spec. A defensive NULL guard protects the migration
    -- against orphan rows during reseeds.
    IF v_owner_id IS NOT NULL AND v_owner_id <> NEW.`user_id` THEN
        INSERT INTO `user_stats` (`user_id`, `num_external_comments`)
        VALUES (NEW.`user_id`, 1)
        ON DUPLICATE KEY UPDATE
            `num_external_comments` = `num_external_comments` + 1;
    END IF;
END //

DELIMITER ;

-- Backfill historical state — INCLUDES soft-deleted rows because the
-- counter is "lifetime cumulative" per the product rule (jamais
-- décrémenté). Joining `media` filters out self-comments.
UPDATE `user_stats` us
   SET us.`num_external_comments` = (
        SELECT COUNT(*)
          FROM `media_comment` mc
          JOIN `media` m ON m.`id` = mc.`media_id`
         WHERE mc.`user_id` = us.`user_id`
           AND mc.`user_id` <> m.`user_id`
   );
