CREATE TABLE IF NOT EXISTS `def_notify_push_subscription` (
`User` char(255) NOT NULL,
`Package` char(255) NOT NULL,
`Module` char(255) DEFAULT '',
`Endpoint` varchar(2048) NOT NULL,
`KeysP256dh` varchar(512) NOT NULL,
`KeysAuth` varchar(512) NOT NULL,
`UserAgent` varchar(512) DEFAULT '',
`CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
`Active` char(1) DEFAULT 'Y',
PRIMARY KEY (`User`, `Package`, `Endpoint`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
INSERT INTO def_notify_push_subscription
(User, Package, Endpoint, KeysP256dh,
KeysAuth, UserAgent, CreatedAt, Active)
VALUES
('{user}', '{package}', '{endpoint}',
'{keys_p256dh}', '{keys_auth}',
'{user_agent}', '{created_at}', 'Y')
ON DUPLICATE KEY UPDATE
KeysP256dh = '{keys_p256dh}',
KeysAuth = '{keys_auth}',
UserAgent = '{user_agent}',
CreatedAt = '{created_at}',
Active = 'Y'
UPDATE def_notify_push_subscription
SET Active = 'N'
WHERE User = '{user}'
AND Package = '{package}'
SELECT Endpoint, KeysP256dh, KeysAuth
FROM def_notify_push_subscription
WHERE User = '{user}'
AND Package = '{package}'
AND Active = 'Y'
SELECT s.User, s.Endpoint, s.KeysP256dh, s.KeysAuth
FROM def_notify_push_subscription s
INNER JOIN sys_usergroup_users g
ON g.User = s.User AND g.Package = s.Package
WHERE g.Usergroup = '{group}'
AND s.Package = '{package}'
AND s.Active = 'Y'