-- Create notification_preferences table with enabled toggles
CREATE TABLE IF NOT EXISTS `notification_preferences` (
  `id` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NOT NULL,
  
  -- Email notification preferences per type
  `emailSystem` TINYINT(1) NOT NULL DEFAULT 1,
  `emailOrder` TINYINT(1) NOT NULL DEFAULT 1,
  `emailPayment` TINYINT(1) NOT NULL DEFAULT 1,
  `emailAffiliate` TINYINT(1) NOT NULL DEFAULT 1,
  `emailFollow` TINYINT(1) NOT NULL DEFAULT 1,
  `emailComment` TINYINT(1) NOT NULL DEFAULT 1,
  `emailLike` TINYINT(1) NOT NULL DEFAULT 1,
  `emailMessage` TINYINT(1) NOT NULL DEFAULT 1,
  `emailAnnouncement` TINYINT(1) NOT NULL DEFAULT 1,
  `emailBookPublished` TINYINT(1) NOT NULL DEFAULT 1,
  `emailBookUnpublished` TINYINT(1) NOT NULL DEFAULT 1,
  `emailBookRejected` TINYINT(1) NOT NULL DEFAULT 1,
  `emailCoauthorInvitation` TINYINT(1) NOT NULL DEFAULT 1,
  `emailCoauthorAccepted` TINYINT(1) NOT NULL DEFAULT 1,
  `emailCoauthorRejected` TINYINT(1) NOT NULL DEFAULT 1,

  -- SMS notification preferences per type
  `smsSystem` TINYINT(1) NOT NULL DEFAULT 0,
  `smsOrder` TINYINT(1) NOT NULL DEFAULT 1,
  `smsPayment` TINYINT(1) NOT NULL DEFAULT 1,
  `smsAffiliate` TINYINT(1) NOT NULL DEFAULT 1,
  `smsFollow` TINYINT(1) NOT NULL DEFAULT 0,
  `smsComment` TINYINT(1) NOT NULL DEFAULT 0,
  `smsLike` TINYINT(1) NOT NULL DEFAULT 0,
  `smsMessage` TINYINT(1) NOT NULL DEFAULT 0,
  `smsAnnouncement` TINYINT(1) NOT NULL DEFAULT 1,
  `smsBookPublished` TINYINT(1) NOT NULL DEFAULT 0,
  `smsBookUnpublished` TINYINT(1) NOT NULL DEFAULT 0,
  `smsBookRejected` TINYINT(1) NOT NULL DEFAULT 0,
  `smsCoauthorInvitation` TINYINT(1) NOT NULL DEFAULT 0,
  `smsCoauthorAccepted` TINYINT(1) NOT NULL DEFAULT 0,
  `smsCoauthorRejected` TINYINT(1) NOT NULL DEFAULT 0,

  -- Enabled toggle per notification type (master switch)
  -- SYSTEM is always enabled (enforced in application logic)
  `enabledSystem` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledOrder` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledPayment` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledAffiliate` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledFollow` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledComment` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledLike` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledMessage` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledAnnouncement` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledBookPublished` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledBookUnpublished` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledBookRejected` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledCoauthorInvitation` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledCoauthorAccepted` TINYINT(1) NOT NULL DEFAULT 1,
  `enabledCoauthorRejected` TINYINT(1) NOT NULL DEFAULT 1,

  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `notification_preferences_userId_key` (`userId`),
  INDEX `notification_preferences_userId_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migration: Add enabled columns to existing notification_preferences table
-- Run this separately if table already exists
-- ALTER TABLE `notification_preferences` 
--   ADD COLUMN `enabledSystem` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledOrder` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledPayment` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledAffiliate` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledFollow` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledComment` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledLike` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledMessage` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledAnnouncement` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledBookPublished` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledBookUnpublished` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledBookRejected` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledCoauthorInvitation` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledCoauthorAccepted` TINYINT(1) NOT NULL DEFAULT 1,
--   ADD COLUMN `enabledCoauthorRejected` TINYINT(1) NOT NULL DEFAULT 1;