-- ============================================================================
-- System Settings Migration
-- Adds table for storing customization settings
-- ============================================================================

-- System Settings Table
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type ENUM('string', 'json', 'boolean', 'integer') DEFAULT 'string',
    description TEXT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_setting_key (setting_key),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('system_name', 'Risk Assessment', 'string', 'System name displayed in header'),
('color_scheme', NULL, 'json', 'Custom color scheme configuration'),
('logo_filename', NULL, 'string', 'Custom logo filename'),
('timezone', 'Europe/London', 'string', 'System timezone'),
('date_format', 'd/m/Y', 'string', 'Date display format'),
('time_format', 'H:i', 'string', 'Time display format')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- Success message
SELECT 'System settings table created successfully!' as Message;
