-- Migration: proper user_notification table (saving + reading notifications,
-- with actor, type, deeplink) plus per-user notification preferences.
--
-- NOTE: `user_notification` previously existed as bare scaffolding (id, user_id,
-- isDeleted) that nothing ever wrote to — safe to drop and recreate with the
-- full schema below.

DROP TABLE IF EXISTS `user_notification`;

CREATE TABLE `user_notification` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,               -- recipient
  `actor_user_id` INT DEFAULT NULL,     -- who triggered it (NULL for system notifications)
  `type` VARCHAR(50) NOT NULL,          -- e.g. follow, pack_like, pack_share, profile_view, pack_removed
  `title` VARCHAR(255) NOT NULL,
  `message` VARCHAR(500) NOT NULL,
  `entity_type` VARCHAR(30) DEFAULT NULL,  -- pack | sticker | user | collection
  `entity_id` INT DEFAULT NULL,
  `deeplink` VARCHAR(255) DEFAULT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `isDeleted` TINYINT(1) DEFAULT 0,
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_notification_user_idx` (`user_id`),
  KEY `user_notification_type_idx` (`type`),
  CONSTRAINT `user_notification_user_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_notification_actor_fk`
    FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- Per-user, per-type notification on/off switches.
-- Absence of a row for (user_id, type) means "enabled" (default-on).
-- --------------------------------------------------------
CREATE TABLE `user_notification_setting` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_notification_setting_user_type_uq` (`user_id`, `type`),
  CONSTRAINT `user_notification_setting_user_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;