-- Migration: Settings and User Management
-- Add settings table and extend users table

-- Settings table for system configuration
CREATE TABLE IF NOT EXISTS settings (
    id SERIAL PRIMARY KEY,
    key VARCHAR(255) UNIQUE NOT NULL,
    value TEXT,
    category VARCHAR(100) DEFAULT 'general',
    description TEXT,
    value_type VARCHAR(50) DEFAULT 'string', -- string, boolean, integer, json
    is_public BOOLEAN DEFAULT false, -- Can be read by non-admin users
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by_user_id INTEGER
);

-- Default settings
INSERT INTO settings (key, value, category, description, value_type, is_public) VALUES
    ('company_name', 'BMC Networks', 'company', 'Firmanavn', 'string', true),
    ('company_cvr', '', 'company', 'CVR-nummer', 'string', false),
    ('company_email', 'info@bmcnetworks.dk', 'company', 'Firma email', 'string', true),
    ('company_phone', '', 'company', 'Firma telefon', 'string', true),
    ('company_address', '', 'company', 'Firma adresse', 'string', false),
    ('vtiger_enabled', 'false', 'integrations', 'vTiger integration aktiv', 'boolean', false),
    ('vtiger_url', '', 'integrations', 'vTiger URL', 'string', false),
    ('vtiger_username', '', 'integrations', 'vTiger brugernavn', 'string', false),
    ('economic_enabled', 'false', 'integrations', 'e-conomic integration aktiv', 'boolean', false),
    ('economic_app_secret', '', 'integrations', 'e-conomic App Secret Token', 'string', false),
    ('economic_agreement_token', '', 'integrations', 'e-conomic Agreement Grant Token', 'string', false),
    ('email_notifications', 'true', 'notifications', 'Email notifikationer', 'boolean', true),
    ('system_timezone', 'Europe/Copenhagen', 'system', 'Tidszone', 'string', true)
ON CONFLICT (key) DO NOTHING;

-- Extend users table with more fields (if not already added by auth migration)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='is_active') THEN
        ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='last_login') THEN
        ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='created_at') THEN
        ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='updated_at') THEN
        ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
    END IF;
END $$;

-- Indexes
CREATE INDEX IF NOT EXISTS idx_settings_category ON settings(category);
CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key);

-- Updated timestamp trigger for settings
CREATE OR REPLACE FUNCTION update_settings_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER settings_updated_at_trigger
    BEFORE UPDATE ON settings
    FOR EACH ROW
    EXECUTE FUNCTION update_settings_updated_at();

COMMENT ON TABLE settings IS 'System configuration and settings';
COMMENT ON COLUMN settings.value_type IS 'Data type: string, boolean, integer, json';
COMMENT ON COLUMN settings.is_public IS 'Whether non-admin users can read this setting';