chattyness/db/schema/functions/001_helpers.sql

-- Chattyness Helper Functions
-- PostgreSQL 18
--
-- Utility functions and common operations
-- Load via: psql -f schema/functions/001_helpers.sql

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- Updated At Trigger Function
-- =============================================================================
-- Automatically updates updated_at column on row modification.
-- =============================================================================

CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.update_updated_at_column() IS
    'Trigger function to automatically update updated_at timestamp';

-- =============================================================================
-- Session Context Functions
-- =============================================================================
-- Functions to get/set session context for RLS policies.
-- Application sets these variables when handling requests.
-- =============================================================================

-- Set current user ID for RLS
CREATE OR REPLACE FUNCTION public.set_current_user_id(user_id UUID)
RETURNS VOID AS $$
BEGIN
    PERFORM set_config('app.current_user_id', user_id::TEXT, false);
END;
$$ LANGUAGE plpgsql;

-- Get current user ID for RLS
CREATE OR REPLACE FUNCTION public.current_user_id()
RETURNS UUID AS $$
BEGIN
    RETURN NULLIF(current_setting('app.current_user_id', true), '')::UUID;
EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Set current realm ID for RLS
CREATE OR REPLACE FUNCTION public.set_current_realm_id(realm_id UUID)
RETURNS VOID AS $$
BEGIN
    PERFORM set_config('app.current_realm_id', realm_id::TEXT, false);
END;
$$ LANGUAGE plpgsql;

-- Get current realm ID for RLS
CREATE OR REPLACE FUNCTION public.current_realm_id()
RETURNS UUID AS $$
BEGIN
    RETURN NULLIF(current_setting('app.current_realm_id', true), '')::UUID;
EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Check if current user is a server admin
CREATE OR REPLACE FUNCTION public.is_server_admin()
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM server.staff
        WHERE user_id = public.current_user_id()
        AND role IN ('owner', 'admin')
    );
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- Check if current user is a server moderator (or higher)
CREATE OR REPLACE FUNCTION public.is_server_moderator()
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM server.staff
        WHERE user_id = public.current_user_id()
        AND role IN ('owner', 'admin', 'moderator')
    );
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- Check if current user is a realm owner/moderator
CREATE OR REPLACE FUNCTION public.is_realm_moderator(check_realm_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    -- Server admins are moderators everywhere
    IF public.is_server_admin() THEN
        RETURN TRUE;
    END IF;

    RETURN EXISTS (
        SELECT 1 FROM realm.memberships
        WHERE realm_id = check_realm_id
        AND user_id = public.current_user_id()
        AND role IN ('owner', 'moderator')
    );
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- Check if current user has membership in a realm
CREATE OR REPLACE FUNCTION public.has_realm_membership(check_realm_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM realm.memberships
        WHERE realm_id = check_realm_id
        AND user_id = public.current_user_id()
    );
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- =============================================================================
-- Friendship Helper Functions
-- =============================================================================

-- Check if two users are friends
CREATE OR REPLACE FUNCTION auth.are_friends(user_a UUID, user_b UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM auth.friendships
        WHERE friend_a = LEAST(user_a, user_b)
        AND friend_b = GREATEST(user_a, user_b)
        AND is_accepted = true
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- Check if user_a has blocked user_b
CREATE OR REPLACE FUNCTION auth.has_blocked(blocker UUID, blocked UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM auth.blocks
        WHERE blocker_id = blocker
        AND blocked_id = blocked
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- Check if either user has blocked the other
CREATE OR REPLACE FUNCTION auth.is_blocked_either_way(user_a UUID, user_b UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM auth.blocks
        WHERE (blocker_id = user_a AND blocked_id = user_b)
           OR (blocker_id = user_b AND blocked_id = user_a)
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- =============================================================================
-- Spatial Helper Functions
-- =============================================================================

-- Create a virtual point from x,y coordinates
CREATE OR REPLACE FUNCTION public.make_virtual_point(x REAL, y REAL)
RETURNS public.virtual_point AS $$
BEGIN
    RETURN ST_SetSRID(ST_MakePoint(x, y), 0)::public.virtual_point;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Create scene bounds from width and height (origin at 0,0)
CREATE OR REPLACE FUNCTION public.make_scene_bounds(width REAL, height REAL)
RETURNS public.scene_bounds AS $$
BEGIN
    RETURN ST_MakeEnvelope(0, 0, width, height, 0)::public.scene_bounds;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Get distance between two virtual points
CREATE OR REPLACE FUNCTION public.virtual_distance(p1 public.virtual_point, p2 public.virtual_point)
RETURNS REAL AS $$
BEGIN
    RETURN ST_Distance(p1, p2)::REAL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Check if a point is within scene bounds
CREATE OR REPLACE FUNCTION public.point_in_bounds(
    point public.virtual_point,
    bounds public.scene_bounds
)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN ST_Within(point, bounds);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- =============================================================================
-- Audit Helper Functions
-- =============================================================================

-- Log an audit event
CREATE OR REPLACE FUNCTION audit.log_event(
    p_category audit.event_category,
    p_action TEXT,
    p_target_type TEXT DEFAULT NULL,
    p_target_id UUID DEFAULT NULL,
    p_details JSONB DEFAULT '{}',
    p_success BOOLEAN DEFAULT TRUE,
    p_error_message TEXT DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_event_id UUID;
BEGIN
    INSERT INTO audit.events (
        category,
        action,
        user_id,
        ip_address,
        target_type,
        target_id,
        realm_id,
        details,
        success,
        error_message
    ) VALUES (
        p_category,
        p_action,
        public.current_user_id(),
        NULLIF(current_setting('app.client_ip', true), '')::INET,
        p_target_type,
        p_target_id,
        public.current_realm_id(),
        p_details,
        p_success,
        p_error_message
    )
    RETURNING id INTO v_event_id;

    RETURN v_event_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION audit.log_event IS 'Helper to create audit log entries';

-- =============================================================================
-- Instance Member Maintenance Functions
-- =============================================================================

-- Clear all instance members (for server startup cleanup)
-- Uses SECURITY DEFINER to bypass RLS
CREATE OR REPLACE FUNCTION scene.clear_all_instance_members()
RETURNS BIGINT AS $$
DECLARE
    deleted_count BIGINT;
BEGIN
    DELETE FROM scene.instance_members;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION scene.clear_all_instance_members() IS
    'Clears all instance members on server startup. Bypasses RLS.';

-- Clear stale instance members based on last_moved_at threshold
-- Uses SECURITY DEFINER to bypass RLS
CREATE OR REPLACE FUNCTION scene.clear_stale_instance_members(threshold_seconds DOUBLE PRECISION)
RETURNS BIGINT AS $$
DECLARE
    deleted_count BIGINT;
BEGIN
    DELETE FROM scene.instance_members
    WHERE last_moved_at < NOW() - make_interval(secs => threshold_seconds);
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION scene.clear_stale_instance_members(DOUBLE PRECISION) IS
    'Clears stale instance members older than threshold. Bypasses RLS.';

-- Grant execute to chattyness_app
GRANT EXECUTE ON FUNCTION scene.clear_all_instance_members() TO chattyness_app;
GRANT EXECUTE ON FUNCTION scene.clear_stale_instance_members(DOUBLE PRECISION) TO chattyness_app;

-- =============================================================================
-- Guest Cleanup Functions
-- =============================================================================

-- Clean up stale guest accounts that haven't been active in 7 days
-- Guests are users with the 'guest' tag in auth.users
-- Uses SECURITY DEFINER to bypass RLS
CREATE OR REPLACE FUNCTION auth.cleanup_stale_guests()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    WITH deleted AS (
        DELETE FROM auth.users
        WHERE 'guest' = ANY(tags)
          AND last_seen_at < now() - interval '7 days'
        RETURNING id
    )
    SELECT count(*) INTO deleted_count FROM deleted;

    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION auth.cleanup_stale_guests() IS
    'Removes guest accounts (users with guest tag) inactive for 7+ days. Run via cron.';

-- Grant execute to chattyness_app
GRANT EXECUTE ON FUNCTION auth.cleanup_stale_guests() TO chattyness_app;

COMMIT;