wulf-pulse/migrations/014_create_admin_settings.sql

-- Admin Settings Tables Migration
-- Creates app_settings, session_policy, and email_template tables

-- App settings table (key-value store for application settings)
CREATE TABLE IF NOT EXISTS "app_settings" (
    id TEXT PRIMARY KEY,
    key TEXT NOT NULL UNIQUE,
    value TEXT,
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Session policy table (CIDR-based session timeout policies)
CREATE TABLE IF NOT EXISTS "session_policy" (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    cidr TEXT NOT NULL,
    timeout_seconds INTEGER NOT NULL DEFAULT 86400,
    priority INTEGER NOT NULL DEFAULT 0,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Email template table
CREATE TABLE IF NOT EXISTS "email_template" (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL UNIQUE,
    subject TEXT NOT NULL,
    body_html TEXT NOT NULL,
    body_text TEXT,
    variables TEXT, -- JSON array of available variables
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Audit log table
CREATE TABLE IF NOT EXISTS "audit_log" (
    id TEXT PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    user_id TEXT REFERENCES "user"(id) ON DELETE SET NULL,
    user_email TEXT,
    action TEXT NOT NULL,
    resource TEXT NOT NULL,
    resource_id TEXT,
    details JSONB,
    ip_address TEXT,
    user_agent TEXT
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_app_settings_key ON "app_settings"(key);
CREATE INDEX IF NOT EXISTS idx_session_policy_priority ON "session_policy"(priority DESC);
CREATE INDEX IF NOT EXISTS idx_email_template_type ON "email_template"(type);
CREATE INDEX IF NOT EXISTS idx_audit_log_timestamp ON "audit_log"(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_audit_log_user_id ON "audit_log"(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON "audit_log"(action);
CREATE INDEX IF NOT EXISTS idx_audit_log_resource ON "audit_log"(resource);

-- Insert default app settings
INSERT INTO "app_settings" (id, key, value, description) VALUES
    ('setting_microsoft_tenant', 'microsoft_tenant_id', 'common', 'Microsoft Entra ID tenant ID'),
    ('setting_session_timeout', 'default_session_timeout', '86400', 'Default session timeout in seconds'),
    ('setting_audit_retention', 'audit_log_retention_days', '90', 'Number of days to retain audit logs')
ON CONFLICT (key) DO NOTHING;

-- Insert default email templates
INSERT INTO "email_template" (id, type, subject, body_html, body_text, variables) VALUES
    ('template_magic_link', 'magic_link', 'Sign in to Pulse', 
     '<h1>Sign in to Pulse</h1><p>Click the link below to sign in:</p><a href="{{url}}">Sign in</a><p>This link expires in 5 minutes.</p>',
     'Sign in to Pulse\n\nClick the link below to sign in:\n{{url}}\n\nThis link expires in 5 minutes.',
     '["url", "email"]'),
    ('template_invitation', 'invitation', 'You''re invited to Pulse',
     '<h1>You''re invited!</h1><p>{{inviter_name}} has invited you to join Pulse.</p><a href="{{url}}">Accept Invitation</a>',
     'You''re invited!\n\n{{inviter_name}} has invited you to join Pulse.\n\nAccept invitation: {{url}}',
     '["url", "email", "inviter_name"]')
ON CONFLICT (type) DO NOTHING;