-- Migration: Add Push Notifications Support
-- Date: 2024
-- Description: Adds user_push_subscriptions table and updates settings table with VAPID keys and notification settings

-- Create user_push_subscriptions table
CREATE TABLE IF NOT EXISTS `user_push_subscriptions` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `endpoint` VARCHAR(500) NOT NULL,
    `p256dh` TEXT NOT NULL,
    `auth` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `user_id` (`user_id`),
    UNIQUE KEY `unique_subscription` (`user_id`, `endpoint`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add new columns to settings table if they don't exist
ALTER TABLE `settings` 
ADD COLUMN IF NOT EXISTS `vapid_public_key` TEXT COMMENT 'Web Push VAPID public key',
ADD COLUMN IF NOT EXISTS `vapid_private_key` TEXT COMMENT 'Web Push VAPID private key',
ADD COLUMN IF NOT EXISTS `smtp_host` VARCHAR(255) COMMENT 'SMTP server hostname',
ADD COLUMN IF NOT EXISTS `smtp_port` INT DEFAULT 587 COMMENT 'SMTP server port',
ADD COLUMN IF NOT EXISTS `smtp_user` VARCHAR(255) COMMENT 'SMTP authentication username',
ADD COLUMN IF NOT EXISTS `smtp_password` TEXT COMMENT 'SMTP authentication password',
ADD COLUMN IF NOT EXISTS `from_email` VARCHAR(255) COMMENT 'Default sender email address',
ADD COLUMN IF NOT EXISTS `sms_username` VARCHAR(255) COMMENT 'SMS gateway username (sms-gate)',
ADD COLUMN IF NOT EXISTS `sms_password` TEXT COMMENT 'SMS gateway password (sms-gate)',
ADD COLUMN IF NOT EXISTS `whatsapp_instance_id` VARCHAR(255) COMMENT 'WhatsApp instance ID (ultramsg.com)',
ADD COLUMN IF NOT EXISTS `whatsapp_api_token` TEXT COMMENT 'WhatsApp API token (ultramsg.com)',
ADD COLUMN IF NOT EXISTS `push_notifications_enabled` BOOLEAN DEFAULT TRUE COMMENT 'Enable browser push notifications',
ADD COLUMN IF NOT EXISTS `email_notifications_enabled` BOOLEAN DEFAULT FALSE COMMENT 'Enable email notifications',
ADD COLUMN IF NOT EXISTS `sms_notifications_enabled` BOOLEAN DEFAULT FALSE COMMENT 'Enable SMS notifications',
ADD COLUMN IF NOT EXISTS `whatsapp_notifications_enabled` BOOLEAN DEFAULT FALSE COMMENT 'Enable WhatsApp notifications';

-- Update notifications table to add data column for flexible storage
ALTER TABLE `notifications`
ADD COLUMN IF NOT EXISTS `data` JSON COMMENT 'Additional notification data as JSON';

-- Create index for faster notification queries
ALTER TABLE `notifications`
ADD INDEX IF NOT EXISTS `idx_user_created` (`user_id`, `created_at`);

-- Insert default VAPID keys if not already set
UPDATE `settings` SET
    `vapid_public_key` = '1zslCAtC2eJWkbu6YjcQkpq38IzzAvaK3FjCArEs2CE',
    `vapid_private_key` = 'MQeQK4d-NKdEz3ULV6zztFZKKkRhrMm62ak0DQjSQI0',
    `push_notifications_enabled` = TRUE
WHERE `id` = 1 AND `vapid_public_key` IS NULL;
