CREATE TABLE `smd__notifications` (
	`notificationID` int(11) unsigned NOT NULL,
	`type` varchar(255) NOT NULL,
	`createDate` timestamp DEFAULT current_timestamp,
	`notificationData` json
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE smd__notifications ADD CONSTRAINT smd__notifications_PK PRIMARY KEY (notificationID);

ALTER TABLE smd__notifications MODIFY COLUMN notificationID int(11) unsigned auto_increment NOT NULL;

DROP TABLE IF EXISTS `smd__user_has_notifications`;

CREATE TABLE `smd__user_has_notifications` (
	`userID` int(11) unsigned NOT NULL,
  	`notificationID` int(11) unsigned NOT NULL,
	`seenAt` timestamp NULL DEFAULT NULL,
	PRIMARY KEY (`userID`,`notificationID`),
	KEY `index_userID` (`userID`),
	KEY `index_notificationID` (`notificationID`),
	KEY `index_seenAt` (`seenAt`),
	CONSTRAINT `FK_user` FOREIGN KEY (`userID`) REFERENCES `cms__user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT `FK_notification` FOREIGN KEY (`notificationID`) REFERENCES `smd__notifications` (`notificationID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE smd__user_has_notifications
    ADD sentAt timestamp NULL DEFAULT NULL;

ALTER TABLE smd__notifications 
	ADD actorID int(11) unsigned;

ALTER TABLE smd__notifications 
	ADD CONSTRAINT FK_actor
	FOREIGN KEY (actorID) REFERENCES cms__user(userID);