CREATE TABLE IF NOT EXISTS `notification_reads` (
    `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `notification_id` INT UNSIGNED NOT NULL,
    `user_id`         INT UNSIGNED NOT NULL,
    `read_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_notification_user` (`notification_id`, `user_id`),
    KEY `idx_notification_id` (`notification_id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migrate existing read receipts for user-targeted notifications
INSERT IGNORE INTO `notification_reads` (`notification_id`, `user_id`, `read_at`)
SELECT n.id, u.id, COALESCE(n.read_at, n.created_at)
FROM notifications n
JOIN users u ON u.id = n.user_id AND u.status = 'active'
WHERE n.is_read = 1
  AND n.user_id IS NOT NULL;