likwid/backend/migrations/20260126330000_federation.sql

-- ============================================================================
-- MULTI-COMMUNITY FEDERATION PLUGIN
-- Cross-instance collaboration, federated decisions, shared governance
-- ============================================================================

CREATE TYPE federation_status AS ENUM ('pending', 'active', 'suspended', 'revoked');
CREATE TYPE sync_direction AS ENUM ('push', 'pull', 'bidirectional');

-- ============================================================================
-- FEDERATED INSTANCES
-- Known remote Likwid instances
-- ============================================================================

CREATE TABLE federated_instances (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    instance_url VARCHAR(500) NOT NULL UNIQUE,
    instance_name VARCHAR(200) NOT NULL,
    instance_description TEXT,
    
    -- Trust & verification
    public_key TEXT,
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,
    verified_at TIMESTAMPTZ,
    trust_level INT NOT NULL DEFAULT 1, -- 1-5
    
    -- Status
    status federation_status NOT NULL DEFAULT 'pending',
    
    -- Capabilities
    supported_features VARCHAR(100)[] DEFAULT '{}',
    protocol_version VARCHAR(20) NOT NULL DEFAULT '1.0',
    
    -- Statistics
    last_sync_at TIMESTAMPTZ,
    sync_failures INT NOT NULL DEFAULT 0,
    total_syncs INT NOT NULL DEFAULT 0,
    
    -- Metadata
    admin_contact VARCHAR(300),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_federated_instances_status ON federated_instances(status);
CREATE INDEX idx_federated_instances_url ON federated_instances(instance_url);

-- ============================================================================
-- COMMUNITY FEDERATIONS
-- Links between local and remote communities
-- ============================================================================

CREATE TABLE community_federations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    local_community_id UUID NOT NULL REFERENCES communities(id) ON DELETE CASCADE,
    remote_instance_id UUID NOT NULL REFERENCES federated_instances(id) ON DELETE CASCADE,
    remote_community_id UUID NOT NULL,
    remote_community_name VARCHAR(200),
    
    -- Configuration
    sync_direction sync_direction NOT NULL DEFAULT 'bidirectional',
    sync_proposals BOOLEAN NOT NULL DEFAULT TRUE,
    sync_votes BOOLEAN NOT NULL DEFAULT FALSE,
    sync_comments BOOLEAN NOT NULL DEFAULT FALSE,
    sync_decisions BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Status
    status federation_status NOT NULL DEFAULT 'pending',
    approved_locally BOOLEAN NOT NULL DEFAULT FALSE,
    approved_remotely BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Mapping
    member_mapping JSONB DEFAULT '{}',
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(local_community_id, remote_instance_id, remote_community_id)
);

CREATE INDEX idx_community_federations_local ON community_federations(local_community_id);
CREATE INDEX idx_community_federations_remote ON community_federations(remote_instance_id);

-- ============================================================================
-- FEDERATED PROPOSALS
-- Proposals shared across federated communities
-- ============================================================================

CREATE TABLE federated_proposals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    federation_id UUID NOT NULL REFERENCES community_federations(id) ON DELETE CASCADE,
    
    -- Local reference
    local_proposal_id UUID REFERENCES proposals(id) ON DELETE SET NULL,
    
    -- Remote reference
    remote_proposal_id UUID NOT NULL,
    remote_url VARCHAR(500),
    
    -- Sync metadata
    is_origin_local BOOLEAN NOT NULL,
    last_synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    sync_hash VARCHAR(64),
    
    -- Status
    sync_status VARCHAR(50) NOT NULL DEFAULT 'synced',
    conflict_detected BOOLEAN NOT NULL DEFAULT FALSE,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_federated_proposals_federation ON federated_proposals(federation_id);
CREATE INDEX idx_federated_proposals_local ON federated_proposals(local_proposal_id);

-- ============================================================================
-- FEDERATED DECISIONS
-- Cross-community voting results
-- ============================================================================

CREATE TABLE federated_decisions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    federated_proposal_id UUID NOT NULL REFERENCES federated_proposals(id) ON DELETE CASCADE,
    
    -- Decision details
    decision_type VARCHAR(50) NOT NULL,
    outcome VARCHAR(50) NOT NULL,
    
    -- Aggregated results (no individual votes)
    participating_communities INT NOT NULL DEFAULT 1,
    total_votes INT NOT NULL DEFAULT 0,
    approval_percentage DECIMAL(5,2),
    
    -- Per-community breakdown
    community_results JSONB NOT NULL DEFAULT '[]',
    
    -- Finalization
    is_final BOOLEAN NOT NULL DEFAULT FALSE,
    finalized_at TIMESTAMPTZ,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_federated_decisions_proposal ON federated_decisions(federated_proposal_id);

-- ============================================================================
-- SYNC LOG
-- Track all federation sync operations
-- ============================================================================

CREATE TABLE federation_sync_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    federation_id UUID REFERENCES community_federations(id) ON DELETE SET NULL,
    instance_id UUID REFERENCES federated_instances(id) ON DELETE SET NULL,
    
    -- Operation
    operation_type VARCHAR(50) NOT NULL,
    direction sync_direction NOT NULL,
    
    -- Results
    records_sent INT NOT NULL DEFAULT 0,
    records_received INT NOT NULL DEFAULT 0,
    conflicts_detected INT NOT NULL DEFAULT 0,
    
    -- Status
    success BOOLEAN NOT NULL,
    error_message TEXT,
    duration_ms INT,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sync_log_federation ON federation_sync_log(federation_id);
CREATE INDEX idx_sync_log_time ON federation_sync_log(created_at);

-- ============================================================================
-- FEDERATION REQUESTS
-- Pending federation requests
-- ============================================================================

CREATE TABLE federation_requests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    
    -- Request details
    from_instance_url VARCHAR(500) NOT NULL,
    from_community_name VARCHAR(200),
    to_community_id UUID REFERENCES communities(id) ON DELETE CASCADE,
    
    -- Message
    request_message TEXT,
    proposed_config JSONB,
    
    -- Status
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    reviewed_by UUID REFERENCES users(id),
    reviewed_at TIMESTAMPTZ,
    review_notes TEXT,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '30 days'
);

CREATE INDEX idx_federation_requests_community ON federation_requests(to_community_id);
CREATE INDEX idx_federation_requests_pending ON federation_requests(status) WHERE status = 'pending';

-- ============================================================================
-- HELPER FUNCTIONS
-- ============================================================================

CREATE OR REPLACE FUNCTION register_federated_instance(
    p_url VARCHAR(500),
    p_name VARCHAR(200),
    p_description TEXT DEFAULT NULL,
    p_public_key TEXT DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
    v_instance_id UUID;
BEGIN
    INSERT INTO federated_instances (instance_url, instance_name, instance_description, public_key)
    VALUES (p_url, p_name, p_description, p_public_key)
    ON CONFLICT (instance_url) DO UPDATE SET
        instance_name = p_name,
        instance_description = COALESCE(p_description, federated_instances.instance_description),
        public_key = COALESCE(p_public_key, federated_instances.public_key),
        updated_at = NOW()
    RETURNING id INTO v_instance_id;
    
    RETURN v_instance_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_community_federation(
    p_local_community_id UUID,
    p_remote_instance_id UUID,
    p_remote_community_id UUID,
    p_remote_community_name VARCHAR(200),
    p_sync_direction sync_direction DEFAULT 'bidirectional'
) RETURNS UUID AS $$
DECLARE
    v_federation_id UUID;
BEGIN
    INSERT INTO community_federations (
        local_community_id, remote_instance_id, remote_community_id,
        remote_community_name, sync_direction
    ) VALUES (
        p_local_community_id, p_remote_instance_id, p_remote_community_id,
        p_remote_community_name, p_sync_direction
    )
    ON CONFLICT (local_community_id, remote_instance_id, remote_community_id) DO UPDATE SET
        remote_community_name = p_remote_community_name,
        sync_direction = p_sync_direction,
        updated_at = NOW()
    RETURNING id INTO v_federation_id;
    
    RETURN v_federation_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_federation_stats(p_community_id UUID)
RETURNS TABLE (
    total_federations BIGINT,
    active_federations BIGINT,
    federated_proposals BIGINT,
    total_syncs BIGINT,
    last_sync TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(DISTINCT cf.id)::BIGINT,
        COUNT(DISTINCT cf.id) FILTER (WHERE cf.status = 'active')::BIGINT,
        COUNT(DISTINCT fp.id)::BIGINT,
        SUM(fi.total_syncs)::BIGINT,
        MAX(fi.last_sync_at)
    FROM community_federations cf
    LEFT JOIN federated_instances fi ON fi.id = cf.remote_instance_id
    LEFT JOIN federated_proposals fp ON fp.federation_id = cf.id
    WHERE cf.local_community_id = p_community_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- VIEWS
-- ============================================================================

CREATE OR REPLACE VIEW active_federations AS
SELECT 
    cf.id,
    cf.local_community_id,
    lc.name AS local_community_name,
    fi.instance_url,
    fi.instance_name,
    cf.remote_community_id,
    cf.remote_community_name,
    cf.sync_direction::text,
    cf.status::text,
    fi.last_sync_at,
    COUNT(fp.id) AS shared_proposals
FROM community_federations cf
JOIN communities lc ON lc.id = cf.local_community_id
JOIN federated_instances fi ON fi.id = cf.remote_instance_id
LEFT JOIN federated_proposals fp ON fp.federation_id = cf.id
WHERE cf.status = 'active'
GROUP BY cf.id, cf.local_community_id, lc.name, fi.instance_url, fi.instance_name,
         cf.remote_community_id, cf.remote_community_name, cf.sync_direction, cf.status, fi.last_sync_at;

CREATE OR REPLACE VIEW federation_health AS
SELECT 
    fi.id AS instance_id,
    fi.instance_url,
    fi.instance_name,
    fi.status::text,
    fi.trust_level,
    fi.last_sync_at,
    fi.sync_failures,
    fi.total_syncs,
    CASE 
        WHEN fi.total_syncs = 0 THEN 0
        ELSE ((fi.total_syncs - fi.sync_failures)::DECIMAL / fi.total_syncs * 100)
    END AS success_rate,
    COUNT(cf.id) AS community_count
FROM federated_instances fi
LEFT JOIN community_federations cf ON cf.remote_instance_id = fi.id AND cf.status = 'active'
GROUP BY fi.id, fi.instance_url, fi.instance_name, fi.status, fi.trust_level,
         fi.last_sync_at, fi.sync_failures, fi.total_syncs;

-- ============================================================================
-- PLUGIN REGISTRATION
-- ============================================================================

INSERT INTO plugins (name, description, version, is_core, is_active, settings_schema)
VALUES (
    'federation',
    'Multi-community federation enabling cross-instance collaboration, shared proposals, and federated decision-making.',
    '1.0.0',
    false,
    true,
    jsonb_build_object(
        'type', 'object',
        'properties', jsonb_build_object(
            'allow_incoming_requests', jsonb_build_object('type', 'boolean', 'default', true),
            'auto_approve_verified', jsonb_build_object('type', 'boolean', 'default', false),
            'min_trust_level', jsonb_build_object('type', 'integer', 'default', 2, 'minimum', 1, 'maximum', 5),
            'sync_interval_minutes', jsonb_build_object('type', 'integer', 'default', 15),
            'share_vote_counts', jsonb_build_object('type', 'boolean', 'default', true),
            'share_individual_votes', jsonb_build_object('type', 'boolean', 'default', false)
        )
    )
) ON CONFLICT (name) DO UPDATE SET version = EXCLUDED.version;