-- System Settings Table
-- Stores system-wide configuration including branding, formulas, and integrations

CREATE TABLE IF NOT EXISTS system_settings (
    setting_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique setting identifier',
    setting_category ENUM('BRANDING', 'CALCULATIONS', 'INTEGRATIONS', 'NOTIFICATIONS', 'GENERAL') NOT NULL,
    setting_name VARCHAR(255) NOT NULL COMMENT 'Human-readable setting name',
    setting_value TEXT DEFAULT NULL COMMENT 'Setting value (JSON for complex data)',
    setting_type ENUM('TEXT', 'NUMBER', 'BOOLEAN', 'JSON', 'FILE') NOT NULL DEFAULT 'TEXT',
    description TEXT DEFAULT NULL COMMENT 'Setting description/help text',
    is_encrypted BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Whether value is encrypted',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (setting_category),
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System-wide configuration settings';

-- Default System Settings
INSERT INTO system_settings (setting_key, setting_category, setting_name, setting_value, setting_type, description) VALUES
-- Branding
('system_name', 'BRANDING', 'System Name', 'Kenyan Pension & Life Insurance Management System', 'TEXT', 'Main system name displayed in headers and emails'),
('system_logo', 'BRANDING', 'System Logo', NULL, 'FILE', 'System logo file path'),
('system_favicon', 'BRANDING', 'Favicon', NULL, 'FILE', 'Favicon file path'),
('company_name', 'BRANDING', 'Company Name', 'Pension Administrator Ltd', 'TEXT', 'Company/organization name'),
('company_address', 'BRANDING', 'Company Address', NULL, 'TEXT', 'Company physical address'),
('company_phone', 'BRANDING', 'Company Phone', NULL, 'TEXT', 'Company contact phone'),
('company_email', 'BRANDING', 'Company Email', NULL, 'TEXT', 'Company contact email'),
('company_website', 'BRANDING', 'Company Website', NULL, 'TEXT', 'Company website URL'),

-- Pension Calculation Formulas
('default_retirement_age', 'CALCULATIONS', 'Default Retirement Age', '60', 'NUMBER', 'Default retirement age for new schemes'),
('default_early_exit_percentage', 'CALCULATIONS', 'Default Early Exit Percentage', '50.00', 'NUMBER', 'Default maximum cash percentage for early exit (Occupational/Umbrella)'),
('default_ipp_early_exit_percentage', 'CALCULATIONS', 'Default IPP Early Exit Percentage', '100.00', 'NUMBER', 'Default maximum cash percentage for IPP early exit'),
('default_commutation_percentage', 'CALCULATIONS', 'Default Commutation Percentage', '30.00', 'NUMBER', 'Default commutation percentage at retirement'),
('contribution_allocation_method', 'CALCULATIONS', 'Contribution Allocation Method', 'PROPORTIONAL', 'TEXT', 'Method for allocating contributions (PROPORTIONAL, EQUAL)'),
('fund_return_allocation_method', 'CALCULATIONS', 'Fund Return Allocation Method', 'PROPORTIONAL_BY_BALANCE', 'TEXT', 'Method for allocating fund returns (PROPORTIONAL_BY_BALANCE, EQUAL)'),

-- M-Pesa Integration
('mpesa_consumer_key', 'INTEGRATIONS', 'M-Pesa Consumer Key', NULL, 'TEXT', 'M-Pesa API consumer key'),
('mpesa_consumer_secret', 'INTEGRATIONS', 'M-Pesa Consumer Secret', NULL, 'TEXT', 'M-Pesa API consumer secret (encrypted)'),
('mpesa_shortcode', 'INTEGRATIONS', 'M-Pesa Shortcode', NULL, 'TEXT', 'M-Pesa business shortcode'),
('mpesa_passkey', 'INTEGRATIONS', 'M-Pesa Passkey', NULL, 'TEXT', 'M-Pesa API passkey (encrypted)'),
('mpesa_environment', 'INTEGRATIONS', 'M-Pesa Environment', 'sandbox', 'TEXT', 'M-Pesa environment (sandbox/production)'),

-- Email Settings
('email_smtp_host', 'NOTIFICATIONS', 'SMTP Host', NULL, 'TEXT', 'SMTP server hostname'),
('email_smtp_port', 'NOTIFICATIONS', 'SMTP Port', '587', 'NUMBER', 'SMTP server port'),
('email_smtp_username', 'NOTIFICATIONS', 'SMTP Username', NULL, 'TEXT', 'SMTP authentication username'),
('email_smtp_password', 'NOTIFICATIONS', 'SMTP Password', NULL, 'TEXT', 'SMTP authentication password (encrypted)'),
('email_from_address', 'NOTIFICATIONS', 'Email From Address', NULL, 'TEXT', 'Default sender email address'),
('email_from_name', 'NOTIFICATIONS', 'Email From Name', NULL, 'TEXT', 'Default sender name'),

-- SMS Settings
('sms_provider', 'NOTIFICATIONS', 'SMS Provider', NULL, 'TEXT', 'SMS service provider (AFRICASTALKING, TWILIO, etc.)'),
('sms_api_key', 'NOTIFICATIONS', 'SMS API Key', NULL, 'TEXT', 'SMS provider API key (encrypted)'),
('sms_sender_id', 'NOTIFICATIONS', 'SMS Sender ID', NULL, 'TEXT', 'SMS sender identifier'),

-- AI Integration
('openai_api_key', 'INTEGRATIONS', 'OpenAI API Key', NULL, 'TEXT', 'OpenAI API key for AI chatbot and predictive features (encrypted)'),
('ai_model', 'INTEGRATIONS', 'AI Model', 'gpt-4', 'TEXT', 'OpenAI model to use (gpt-4, gpt-3.5-turbo, gpt-4-turbo)'),
('ai_enabled', 'INTEGRATIONS', 'AI Features Enabled', '1', 'BOOLEAN', 'Enable AI chatbot and predictive features'),

-- eTIMS Integration
('etims_base_url', 'INTEGRATIONS', 'eTIMS Base URL', 'http://62.169.23.132:4122', 'TEXT', 'eTIMS API base URL'),
('etims_api_key', 'INTEGRATIONS', 'eTIMS API Key', NULL, 'TEXT', 'eTIMS API key (encrypted)'),
('etims_business_pin', 'INTEGRATIONS', 'eTIMS Business PIN', NULL, 'TEXT', 'KRA Business PIN'),
('etims_branch_id', 'INTEGRATIONS', 'eTIMS Branch ID', '00', 'TEXT', 'Branch identifier'),
('etims_mock_mode', 'INTEGRATIONS', 'eTIMS Mock Mode', '0', 'BOOLEAN', 'Enable mock mode for testing (no actual API calls)'),

-- General Settings
('currency_symbol', 'GENERAL', 'Currency Symbol', 'KES', 'TEXT', 'System currency symbol'),
('currency_code', 'GENERAL', 'Currency Code', 'KES', 'TEXT', 'System currency code'),
('date_format', 'GENERAL', 'Date Format', 'd/m/Y', 'TEXT', 'System date format'),
('timezone', 'GENERAL', 'Timezone', 'Africa/Nairobi', 'TEXT', 'System timezone'),
('pagination_per_page', 'GENERAL', 'Records Per Page', '20', 'NUMBER', 'Default number of records per page'),
('session_timeout', 'GENERAL', 'Session Timeout (minutes)', '30', 'NUMBER', 'User session timeout in minutes'),
('enable_audit_logging', 'GENERAL', 'Enable Audit Logging', '1', 'BOOLEAN', 'Enable comprehensive audit logging'),
('enable_email_notifications', 'GENERAL', 'Enable Email Notifications', '1', 'BOOLEAN', 'Enable email notifications'),
('enable_sms_notifications', 'GENERAL', 'Enable SMS Notifications', '0', 'BOOLEAN', 'Enable SMS notifications');
