-- Email Aliases Migration
-- Create email alias system for routing emails to users and distribution lists

-- Email aliases table
CREATE TABLE IF NOT EXISTS email_aliases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    alias_email VARCHAR(255) NOT NULL UNIQUE COMMENT 'The email alias (e.g., sales@mavrix.one)',
    description VARCHAR(500) NULL COMMENT 'Description of what this alias is for',
    is_active TINYINT(1) DEFAULT 1 COMMENT 'Whether this alias is active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_alias_email (alias_email),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Email aliases for routing to users';

-- Email alias recipients (many-to-many for distribution lists)
CREATE TABLE IF NOT EXISTS email_alias_recipients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    alias_id INT NOT NULL,
    user_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (alias_id) REFERENCES email_aliases(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_alias_user (alias_id, user_id),
    INDEX idx_alias_id (alias_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Users assigned to receive emails from aliases';

-- Insert some common aliases as examples
INSERT INTO email_aliases (alias_email, description, is_active) VALUES
('info@mavrix.one', 'General information inquiries', 1),
('support@mavrix.one', 'Customer support', 1),
('sales@mavrix.one', 'Sales inquiries', 1),
('billing@mavrix.one', 'Billing and payments', 1)
ON DUPLICATE KEY UPDATE alias_email = alias_email;