feat: add notifications inbox for actors

closes #215 (closed)

SET AUTOCOMMIT = 0;
START TRANSACTION;

CREATE TABLE `cp_notifications` (
  `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `actor_id` INT UNSIGNED NOT NULL,
  `target_actor_id` INT UNSIGNED NOT NULL,
  `post_id` binary(16) DEFAULT NULL,
  `activity_id` binary(16) NOT NULL,
  `type` enum('like','follow','share','reply') NOT NULL,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  FOREIGN KEY (`actor_id`) REFERENCES `cp_fediverse_actors`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`target_actor_id`) REFERENCES `cp_fediverse_actors`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`post_id`) REFERENCES `cp_fediverse_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`activity_id`) REFERENCES `cp_fediverse_activities`(`id`) ON DELETE CASCADE
);

DELIMITER $$
CREATE TRIGGER `cp_fediverse_activities_after_insert` AFTER INSERT ON `cp_fediverse_activities` FOR EACH ROW BEGIN
-- only create notification if new incoming activity with NULL status is created
IF NEW.target_actor_id AND NEW.target_actor_id != NEW.actor_id AND NEW.status IS NULL THEN
    IF NEW.type IN ( 'Create', 'Like', 'Announce', 'Follow' ) THEN
        SET @type = (CASE
                        WHEN NEW.type = 'Create' THEN 'reply'
                        WHEN NEW.type = 'Like' THEN 'like'
                        WHEN NEW.type = 'Announce' THEN 'share'
                        WHEN NEW.type = 'Follow' THEN 'follow'
                    END);
        INSERT INTO `cp_notifications` (`actor_id`, `target_actor_id`, `post_id`, `activity_id`, `type`, `created_at`, `updated_at`)
            VALUES (NEW.actor_id, NEW.target_actor_id, NEW.post_id, NEW.id, @type, NEW.created_at, NEW.created_at);
    ELSE
        DELETE FROM `cp_notifications`
        WHERE `actor_id` = NEW.actor_id
        AND `target_actor_id` = NEW.target_actor_id
        AND ((`type` = (CASE WHEN NEW.type = 'Undo_Follow' THEN 'follow' END) AND `post_id` IS NULL)
            OR (`type` = (CASE
                            WHEN NEW.type = 'Delete' THEN 'reply'
                            WHEN NEW.type = 'Undo_Like' THEN 'like'
                            WHEN NEW.type = 'Undo_Announce' THEN 'share'
                        END)
                AND `post_id` = NEW.post_id));
    END IF;
END IF;
END
$$
DELIMITER ;

UPDATE cp_fediverse_activities SET type=CONCAT('Undo_',json_value(payload, '$.object.type')) WHERE type='Undo';

INSERT INTO `cp_notifications`(`actor_id`, `target_actor_id`, `post_id`, `activity_id`, `created_at`, `updated_at`, `type`) 
	SELECT activities1.actor_id, activities1.target_actor_id, activities1.post_id, activities1.id, activities1.created_at, activities1.created_at, (CASE
	WHEN activities1.type = 'Create' THEN 'reply'
	WHEN activities1.type = 'Favourite' THEN 'like'
	WHEN activities1.type = 'Announce' THEN 'share'
	WHEN activities1.type = 'Follow' THEN 'follow'
END)
	FROM `cp_fediverse_activities` activities1
	WHERE `status` IS NULL
    	AND `target_actor_id` IS NOT NULL
	AND `target_actor_id` != `actor_id`
	AND `type` IN ( 'Create', 'Like', 'Announce', 'Follow' )
	AND NOT EXISTS (
		SELECT * FROM `cp_fediverse_activities` activities2 
		WHERE activities2.actor_id = activities1.actor_id
		AND activities2.post_id = activities1.post_id
		AND activities2.type = CONCAT('Undo_', activities1.type));

COMMIT;