-- Likes/dislikes feed for media + denormalized counters table.
--
-- Design notes:
--   - One user can only hold ONE reaction per media (PK `(media_id, user_id)`).
--     A flip (like → dislike) goes through UPDATE so the historical row is
--     reused; the trigger reconciles the counters accordingly.
--   - `value` uses MySQL ENUM rather than a tinyint+lookup because the set is
--     2 cases that won't grow; ENUM keeps the index footprint identical (1B)
--     while the column reads as itself in ad-hoc SQL.
--   - Self-react protection lives in the service layer (see
--     `MediaReactionService::react`). It is NOT enforced by a DB constraint
--     because we want a clean 403 response, not a generic FK violation.
--
-- `media_stats` mirrors the `user_stats` pattern: the application code never
-- writes to it directly — only the three triggers below adjust the counters.
-- `views_count` is included now even though no view tracking exists yet,
-- because adding a counter column later costs the same migration; keeping
-- the column avoids churning the table when view tracking lands.

CREATE TABLE `media_reaction` (
    `media_id`   BINARY(16)              NOT NULL,
    `user_id`    BINARY(16)              NOT NULL,
    `value`      ENUM('like','dislike')  NOT NULL,
    `created_at` DATETIME                NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`media_id`, `user_id`),
    -- Powers `GET /api/media/{mediaId}/likes|dislikes` listings (keyset on
    -- `created_at DESC, user_id DESC` filtered by value).
    KEY `idx_mr_media_value_created` (`media_id`, `value`, `created_at`, `user_id`),
    -- Powers `GET /api/users/me/likes|dislikes` listings (same keyset, filtered
    -- by user + value).
    KEY `idx_mr_user_value_created`  (`user_id`, `value`, `created_at`, `media_id`),
    CONSTRAINT `fk_mr_media` FOREIGN KEY (`media_id`) REFERENCES `media`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mr_user`  FOREIGN KEY (`user_id`)  REFERENCES `user`(`id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `media_stats` (
    `media_id`       BINARY(16) NOT NULL,
    `likes_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `dislikes_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `views_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`media_id`),
    CONSTRAINT `fk_ms_media` FOREIGN KEY (`media_id`) REFERENCES `media`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Triggers maintain the counters. Each one ensures the `media_stats` row
-- exists via INSERT ... ON DUPLICATE KEY UPDATE before adjusting — that way
-- the first reaction on a media auto-provisions its counter row.

DELIMITER //

CREATE TRIGGER `t_add_media_reaction`
AFTER INSERT ON `media_reaction`
FOR EACH ROW
BEGIN
    IF NEW.value = 'like' THEN
        INSERT INTO `media_stats` (`media_id`, `likes_count`)
            VALUES (NEW.media_id, 1)
            ON DUPLICATE KEY UPDATE `likes_count` = `likes_count` + 1;
    ELSE
        INSERT INTO `media_stats` (`media_id`, `dislikes_count`)
            VALUES (NEW.media_id, 1)
            ON DUPLICATE KEY UPDATE `dislikes_count` = `dislikes_count` + 1;
    END IF;
END//

CREATE TRIGGER `t_update_media_reaction`
AFTER UPDATE ON `media_reaction`
FOR EACH ROW
BEGIN
    -- Flip handler. If `value` did not actually change, do nothing — keeps
    -- idempotent UPDATEs cheap.
    IF OLD.value <> NEW.value THEN
        IF NEW.value = 'like' THEN
            UPDATE `media_stats`
               SET `likes_count`    = `likes_count` + 1,
                   `dislikes_count` = GREATEST(`dislikes_count` - 1, 0)
             WHERE `media_id` = NEW.media_id;
        ELSE
            UPDATE `media_stats`
               SET `dislikes_count` = `dislikes_count` + 1,
                   `likes_count`    = GREATEST(`likes_count` - 1, 0)
             WHERE `media_id` = NEW.media_id;
        END IF;
    END IF;
END//

CREATE TRIGGER `t_delete_media_reaction`
AFTER DELETE ON `media_reaction`
FOR EACH ROW
BEGIN
    IF OLD.value = 'like' THEN
        UPDATE `media_stats`
           SET `likes_count` = GREATEST(`likes_count` - 1, 0)
         WHERE `media_id` = OLD.media_id;
    ELSE
        UPDATE `media_stats`
           SET `dislikes_count` = GREATEST(`dislikes_count` - 1, 0)
         WHERE `media_id` = OLD.media_id;
    END IF;
END//

DELIMITER ;
