likwid/backend/migrations/20260126120000_instance_settings.sql

-- Instance-level settings for platform configuration
-- Determines how the platform operates globally

CREATE TABLE IF NOT EXISTS instance_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    
    -- Setup status
    setup_completed BOOLEAN NOT NULL DEFAULT FALSE,
    setup_completed_at TIMESTAMPTZ,
    setup_completed_by UUID REFERENCES users(id),
    
    -- Instance identity
    instance_name VARCHAR(100) NOT NULL DEFAULT 'Likwid',
    instance_description TEXT,
    instance_logo_url TEXT,
    
    -- Platform mode: 'open', 'approval', 'admin_only', 'single_community'
    -- open: Any user can create communities
    -- approval: Users can request to create communities, admin approves
    -- admin_only: Only admins can create communities
    -- single_community: Platform is dedicated to one community (no creation UI)
    platform_mode VARCHAR(20) NOT NULL DEFAULT 'open',
    
    -- Single community mode settings
    single_community_id UUID REFERENCES communities(id),
    
    -- Registration settings
    registration_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    registration_mode VARCHAR(20) NOT NULL DEFAULT 'open', -- 'open', 'invite_only', 'approval'
    require_email_verification BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Community creation settings (when platform_mode allows)
    default_community_visibility VARCHAR(20) NOT NULL DEFAULT 'public', -- 'public', 'private'
    allow_private_communities BOOLEAN NOT NULL DEFAULT TRUE,
    max_communities_per_user INT, -- NULL = unlimited
    
    -- Plugin policy defaults
    default_plugin_policy VARCHAR(20) NOT NULL DEFAULT 'curated', -- 'open', 'curated', 'admin_only'
    allow_unsigned_plugins BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Moderation defaults
    default_moderation_mode VARCHAR(20) NOT NULL DEFAULT 'standard', -- 'minimal', 'standard', 'strict'
    
    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Ensure only one row exists
CREATE UNIQUE INDEX IF NOT EXISTS idx_instance_settings_singleton ON instance_settings ((TRUE));

-- Community-specific settings that override instance defaults
CREATE TABLE IF NOT EXISTS community_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    community_id UUID NOT NULL UNIQUE REFERENCES communities(id) ON DELETE CASCADE,
    
    -- Membership
    membership_mode VARCHAR(20) NOT NULL DEFAULT 'open', -- 'open', 'approval', 'invite_only'
    allow_member_invites BOOLEAN NOT NULL DEFAULT TRUE,
    max_members INT, -- NULL = unlimited
    
    -- Content & Moderation
    moderation_mode VARCHAR(20) NOT NULL DEFAULT 'standard', -- 'minimal', 'standard', 'strict', 'custom'
    require_post_approval BOOLEAN NOT NULL DEFAULT FALSE,
    allow_anonymous_viewing BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Governance
    governance_model VARCHAR(20) NOT NULL DEFAULT 'standard', -- 'standard', 'democratic', 'delegated', 'custom'
    voting_threshold_percent INT NOT NULL DEFAULT 50,
    proposal_duration_days INT NOT NULL DEFAULT 7,
    quorum_percent INT, -- NULL = no quorum required
    
    -- Plugins
    plugin_policy VARCHAR(20) NOT NULL DEFAULT 'inherit', -- 'inherit', 'open', 'curated', 'admin_only', 'disabled'
    allowed_plugin_ids UUID[], -- If curated, list of allowed plugin package IDs
    
    -- Features toggles
    features_enabled JSONB NOT NULL DEFAULT '{
        "proposals": true,
        "voting": true,
        "comments": true,
        "notifications": true,
        "plugins": true
    }'::jsonb,
    
    -- Custom rules (JSON schema for flexibility)
    custom_rules JSONB NOT NULL DEFAULT '[]'::jsonb,
    
    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_community_settings_community ON community_settings(community_id);

-- Insert default instance settings row
INSERT INTO instance_settings (id) 
VALUES (uuid_generate_v4())
ON CONFLICT DO NOTHING;

-- Trigger to update updated_at
CREATE OR REPLACE FUNCTION update_settings_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER instance_settings_updated
    BEFORE UPDATE ON instance_settings
    FOR EACH ROW EXECUTE FUNCTION update_settings_timestamp();

CREATE TRIGGER community_settings_updated
    BEFORE UPDATE ON community_settings
    FOR EACH ROW EXECUTE FUNCTION update_settings_timestamp();