-- Function to update user activity timestamp
-- Updates active_last timestamp for app_user (mobile app) or updated_at for user (web portal)
-- This function should be called periodically (every 5 minutes) or on user actions
--
-- Parameters:
--   p_user_id: UUID of the user (from auth.users.id)
--   p_platform: 'app' for mobile app users, 'web' for web portal users
--
-- Returns: Boolean indicating success

CREATE OR REPLACE FUNCTION update_user_activity(
    p_user_id UUID,
    p_platform TEXT DEFAULT 'app'
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    -- Update app_user table (mobile app users)
    IF p_platform = 'app' THEN
        UPDATE app_user
        SET active_last = NOW()
        WHERE id = p_user_id
        AND archived = 0;
        
        -- Return true if row was updated
        RETURN FOUND;
    END IF;
    
    -- Update user table (web portal users)
    IF p_platform = 'web' THEN
        UPDATE "user"
        SET updated_at = NOW()
        WHERE id = p_user_id
        AND active IS NOT NULL;
        
        -- Return true if row was updated
        RETURN FOUND;
    END IF;
    
    -- If platform doesn't match, return false
    RETURN FALSE;
END;
$$;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION update_user_activity(UUID, TEXT) TO authenticated;

-- Comment
COMMENT ON FUNCTION update_user_activity IS 'Updates user activity timestamp for tracking active users. Platform: app (mobile) or web (portal)';

