-- Create notification preferences table
CREATE TABLE IF NOT EXISTS notification_preferences (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    category VARCHAR(50) NOT NULL,
    channel VARCHAR(20) NOT NULL,
    enabled BOOLEAN DEFAULT true,
    frequency VARCHAR(20) DEFAULT 'instant',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_pref (user_id, category, channel),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create notification categories table
CREATE TABLE IF NOT EXISTS notification_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    is_required BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_category (name)
);

-- Insert default notification categories
INSERT INTO notification_categories (name, description, icon, is_required) VALUES
('security', 'Security alerts and important account notifications', 'shield-alt', true),
('account_activity', 'Login attempts and account changes', 'user-shield', false),
('pattern_updates', 'Updates about your saved patterns', 'project-diagram', false),
('system_updates', 'System maintenance and feature updates', 'info-circle', false),
('promotional', 'News, updates, and promotional content', 'bullhorn', false);

-- Create notification channels table
CREATE TABLE IF NOT EXISTS notification_channels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_channel (name)
);

-- Insert default notification channels
INSERT INTO notification_channels (name, description, icon) VALUES
('email', 'Email notifications', 'envelope'),
('web', 'In-app notifications', 'bell'),
('mobile', 'Mobile push notifications', 'mobile-alt');

-- Create notification templates table
CREATE TABLE IF NOT EXISTS notification_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    channel VARCHAR(20) NOT NULL,
    subject_template TEXT,
    body_template TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES notification_categories(id),
    UNIQUE KEY unique_template (category_id, channel)
);

-- Insert default email templates
INSERT INTO notification_templates (category_id, channel, subject_template, body_template) 
SELECT 
    nc.id,
    'email',
    CASE nc.name
        WHEN 'security' THEN 'Security Alert: {subject}'
        WHEN 'account_activity' THEN 'Account Activity: {subject}'
        WHEN 'pattern_updates' THEN 'Pattern Update: {subject}'
        WHEN 'system_updates' THEN 'System Update: {subject}'
        WHEN 'promotional' THEN '{subject}'
    END,
    CASE nc.name
        WHEN 'security' THEN 'Dear {username},\n\nThis is a security notification regarding your account.\n\n{message}\n\nIf you did not perform this action, please contact support immediately.\n\nBest regards,\nNumber Generator Tool Team'
        WHEN 'account_activity' THEN 'Dear {username},\n\n{message}\n\nIf this was not you, please secure your account immediately.\n\nBest regards,\nNumber Generator Tool Team'
        WHEN 'pattern_updates' THEN 'Dear {username},\n\n{message}\n\nVisit your dashboard to view the changes.\n\nBest regards,\nNumber Generator Tool Team'
        WHEN 'system_updates' THEN 'Dear {username},\n\n{message}\n\nThank you for using our service.\n\nBest regards,\nNumber Generator Tool Team'
        WHEN 'promotional' THEN 'Dear {username},\n\n{message}\n\nBest regards,\nNumber Generator Tool Team\n\nTo unsubscribe from promotional emails, visit your notification preferences.'
    END
FROM notification_categories nc;
